creating and deleting a 2nd instance of Access

T

Tcs

I have a task that returns a large number of rows. To keep my primary mdb
reasonably clean, I want to create a new mdb, create (or copy) my one table,
then delete the mdb once I'm done. (Actually, I'd probably be better off
deleting the temp mdb, if it exists, BEFORE I create it. (So I could work with
the contents more easily.)

Here's my code:

Private Function fncCreateTempDatabase()
On Error GoTo Err_fncCreateTempDatabase

Dim DefaultWorkspace As Workspace
Dim CurrentDatabase As Database, MyTempDatabase As Database

Set DefaultWorkspace = DBEngine.Workspaces(0)

Set MyTempDatabase = DefaultWorkspace.CreateDatabase("AS400_CIS_temp.mdb",
DB_LANG_GENERAL)

Call fncCreateTable


Exit_fncCreateTempDatabase:

Exit Function

Err_fncCreateTempDatabase:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source & vbCrLf
& Err.Description, , "RunJob - fncCreateTempDatabase - " & Date & " - " & Time
' intRunJobLElecRpt4Andy = False
Resume Exit_fncCreateTempDatabase

End Function


Private Function fncCreateTable()
On Error GoTo Err_fncCreateTable

Dim MyTableDef As TableDef, MyField As Field
Dim MyTempDatabase As Database

' Make MyTempDatabase the 'TEMP' DB and create a new table.
Set MyTempDatabase = DBEngine.Workspaces(1).Databases(1)

Set MyTableDef = MyTempDatabase.CreateTableDef("tblAcctsRecAging_Details")

Set MyField = MyTableDef.CreateField("TheNewField", DB_TEXT)
Set MyField = MyTableDef.CreateField("CustID", dbDecimal, auto)
Set MyField = MyTableDef.CreateField("LocID", dbDecimal, auto)
Set MyField = MyTableDef.CreateField("CustClass", dbText, 2)
Set MyField = MyTableDef.CreateField("Serv", dbText, 2)
Set MyField = MyTableDef.CreateField("PeriodYY", dbLong, auto)
Set MyField = MyTableDef.CreateField("PeriodMM", dbLong, auto)
Set MyField = MyTableDef.CreateField("AgeCode", dbText, 4)
Set MyField = MyTableDef.CreateField("ChgType", dbText, 2)
Set MyField = MyTableDef.CreateField("ChgDesc", dbText, 20)
Set MyField = MyTableDef.CreateField("CurrentCount", dbLong, auto)
Set MyField = MyTableDef.CreateField("CurrentAmtBilled", dbLong, auto)
Set MyField = MyTableDef.CreateField("CurrentUnPaid", dbLong, auto)
Set MyField = MyTableDef.CreateField("30dayCount", dbLong, auto)
Set MyField = MyTableDef.CreateField("30dayAmtBilled", dbLong, auto)
Set MyField = MyTableDef.CreateField("30dayUnPaid", dbLong, auto)
Set MyField = MyTableDef.CreateField("60dayCount", dbLong, auto)
Set MyField = MyTableDef.CreateField("60dayAmtBilled", dbLong, auto)
Set MyField = MyTableDef.CreateField("60dayUnPaid", dbLong, auto)
Set MyField = MyTableDef.CreateField("90dayCount", dbLong, auto)
Set MyField = MyTableDef.CreateField("90dayAmtBilled", dbLong, auto)
Set MyField = MyTableDef.CreateField("90dayUnPaid", dbLong, auto)
Set MyField = MyTableDef.CreateField("Over90Count", dbLong, auto)
Set MyField = MyTableDef.CreateField("Over90AmtBilled", dbLong, auto)
Set MyField = MyTableDef.CreateField("Over90UnPaid", dbLong, auto)
Set MyField = MyTableDef.CreateField("DateRetrieved", dbDate, general)

MyTableDef.Fields.Append MyField

' Add table to the collection.
MyDatabase.TableDefs.Append MyTableDef

Exit_fncCreateTable:

Exit Function

Err_fncCreateTable:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source & vbCrLf
& Err.Description, , "RunJob - fncCreateTable - " & Date & " - " & Time
' intRunJobLElecRpt4Andy = False
Resume Exit_fncCreateTable

End Function

When I create my table, I want to create it in the TEMP db, not the CURRENT db.
Do I use this?

Set MyTempDatabase = DBEngine.Workspaces(1).Databases(1)

Rather than:

Set MyTempDatabase = DBEngine.Workspaces(0).Databases(0) ??

I'm ASSUMING that the "(0)" means the 'current' db, but I'm just guessing. I've
tried looking for "DBEngine", and can't find anything. I've tried looking for
"CreateDatabase", and don't find what I need. (I've tried the newsgroups,
Access help, and my book, the "Complete Reference".)

Access help doesn't show me anything except "CreateDatabase Method" in the
lower left pane of help. There are no explanations. No help.
I have several books, including the "Complete Reference" for Access 2000. I
can't find "CreateDatabase". So I try looking up "Methods". There's less than
one page on Methods, in a book of 1319 pages. And not so much as a list of
Methods.

When I create the fields in my table, do I need to append each field after I
"set" each field? Or is there an easy way to append ALL the fields? (Number
"MyField" from 1 thru 26 then "append" MyField1 thru MyField26 with a separate
append for each?)

After I create the new 'temp' db, do I have to "open" it in order to create my
new table? And do I have to "close" it before I "kill" it? (I would ASSUME
yes.)

And lastly, where should I be looking when I search for help. I normally try
newsgroups, Access, Google and my books, but I'm not having any luck. Should I
be looking at some MS website? Wouldn't Google find what I'm after, even if it
IS MS?

Any suggestions, fixes, thoughts would be VERY MUCH appreciated. When I don't
have any docs to help me, I get really uncomfortable. And I'm feeling REALLY
uncomnfortable right now. :(

Thanks in advance,

Tom
 
D

Douglas J. Steele

Answers inline.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Tcs said:
I have a task that returns a large number of rows. To keep my primary mdb
reasonably clean, I want to create a new mdb, create (or copy) my one table,
then delete the mdb once I'm done. (Actually, I'd probably be better off
deleting the temp mdb, if it exists, BEFORE I create it. (So I could work with
the contents more easily.)

Here's my code:

Private Function fncCreateTempDatabase()
On Error GoTo Err_fncCreateTempDatabase

Dim DefaultWorkspace As Workspace
Dim CurrentDatabase As Database, MyTempDatabase As Database

Set DefaultWorkspace = DBEngine.Workspaces(0)

Set MyTempDatabase = DefaultWorkspace.CreateDatabase("AS400_CIS_temp.mdb",
DB_LANG_GENERAL)

Call fncCreateTable


Exit_fncCreateTempDatabase:

Exit Function

Err_fncCreateTempDatabase:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source & vbCrLf
& Err.Description, , "RunJob - fncCreateTempDatabase - " & Date & " - " & Time
' intRunJobLElecRpt4Andy = False
Resume Exit_fncCreateTempDatabase

End Function


Private Function fncCreateTable()
On Error GoTo Err_fncCreateTable

Dim MyTableDef As TableDef, MyField As Field
Dim MyTempDatabase As Database

' Make MyTempDatabase the 'TEMP' DB and create a new table.
Set MyTempDatabase = DBEngine.Workspaces(1).Databases(1)
Click to expand...

Instead of

Set MyTempDatabase = DBEngine.Workspaces(1).Databases(1)

Try

Set MyTempDatabase = OpenDatabase("AS400_CIS_temp.mdb")

Alternatively, you could pass MyTempDatabase from fncCreateTempDatabase to
Call fncCreateTable. Change the line

Call fncCreateTable

to

Call fncCreateTable(MyTempDatabase)

in fncCreateTempDatabase, change the declaration of fncCreateTempDatabase
from

Private Function fncCreateTable()

to

Private Function fncCreateTable(MyTempDatabase As Database)

and remove the following from fncCreateTable

Dim MyTempDatabase As Database

' Make MyTempDatabase the 'TEMP' DB and create a new table.
Set MyTempDatabase = DBEngine.Workspaces(1).Databases(1)

Set MyTableDef = MyTempDatabase.CreateTableDef("tblAcctsRecAging_Details")

Set MyField = MyTableDef.CreateField("TheNewField", DB_TEXT)
Set MyField = MyTableDef.CreateField("CustID", dbDecimal, auto)
Set MyField = MyTableDef.CreateField("LocID", dbDecimal, auto)
Set MyField = MyTableDef.CreateField("CustClass", dbText, 2)
Set MyField = MyTableDef.CreateField("Serv", dbText, 2)
Set MyField = MyTableDef.CreateField("PeriodYY", dbLong, auto)
Set MyField = MyTableDef.CreateField("PeriodMM", dbLong, auto)
Set MyField = MyTableDef.CreateField("AgeCode", dbText, 4)
Set MyField = MyTableDef.CreateField("ChgType", dbText, 2)
Set MyField = MyTableDef.CreateField("ChgDesc", dbText, 20)
Set MyField = MyTableDef.CreateField("CurrentCount", dbLong, auto)
Set MyField = MyTableDef.CreateField("CurrentAmtBilled", dbLong, auto)
Set MyField = MyTableDef.CreateField("CurrentUnPaid", dbLong, auto)
Set MyField = MyTableDef.CreateField("30dayCount", dbLong, auto)
Set MyField = MyTableDef.CreateField("30dayAmtBilled", dbLong, auto)
Set MyField = MyTableDef.CreateField("30dayUnPaid", dbLong, auto)
Set MyField = MyTableDef.CreateField("60dayCount", dbLong, auto)
Set MyField = MyTableDef.CreateField("60dayAmtBilled", dbLong, auto)
Set MyField = MyTableDef.CreateField("60dayUnPaid", dbLong, auto)
Set MyField = MyTableDef.CreateField("90dayCount", dbLong, auto)
Set MyField = MyTableDef.CreateField("90dayAmtBilled", dbLong, auto)
Set MyField = MyTableDef.CreateField("90dayUnPaid", dbLong, auto)
Set MyField = MyTableDef.CreateField("Over90Count", dbLong, auto)
Set MyField = MyTableDef.CreateField("Over90AmtBilled", dbLong, auto)
Set MyField = MyTableDef.CreateField("Over90UnPaid", dbLong, auto)
Set MyField = MyTableDef.CreateField("DateRetrieved", dbDate, general)

MyTableDef.Fields.Append MyField
Click to expand...

Since you've only got one MyField object, this will only add the last field
(DateRetrieved) to your table.

Try:

Set MyTableDef =
MyTempDatabase.CreateTableDef("tblAcctsRecAging_Details")

With MyTableDef
.Fields.Append .CreateField("TheNewField", DB_TEXT)
.Fields.Append .CreateField("CustID", dbDecimal, auto)
etc.
End With

' Add table to the collection.
MyDatabase.TableDefs.Append MyTableDef

Exit_fncCreateTable:

Exit Function

Err_fncCreateTable:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source & vbCrLf
& Err.Description, , "RunJob - fncCreateTable - " & Date & " - " & Time
' intRunJobLElecRpt4Andy = False
Resume Exit_fncCreateTable

End Function

After I create the new 'temp' db, do I have to "open" it in order to create my
new table? And do I have to "close" it before I "kill" it? (I would ASSUME
yes.)
Click to expand...

Yes to both (but see above)
And lastly, where should I be looking when I search for help. I normally try
newsgroups, Access, Google and my books, but I'm not having any luck. Should I
be looking at some MS website? Wouldn't Google find what I'm after, even if it
IS MS?
Click to expand...

Google isn't MS.

I'll agree that finding things is a bit of a "black art" at times, though.
It takes practise to learn how to write effective searches in Google.
 
T

Tcs

Thank you.
Google isn't MS.
Click to expand...
I'll agree that finding things is a bit of a "black art" at times, though.
It takes practise to learn how to write effective searches in Google.
Click to expand...

I know Google isn't MS, but I confess I WAS kind of assuming that Google would
find answers on MS websites, since it has done so before. Perhaps I need to try
MS's TechNet site as part of search procedure.

In any event,

Thanks so much.
 
D

Douglas J. Steele

Tcs said:
Thank you.



I know Google isn't MS, but I confess I WAS kind of assuming that Google would
find answers on MS websites, since it has done so before. Perhaps I need to try
MS's TechNet site as part of search procedure.
Click to expand...

Actually, in the past Google tended to do a better job finding stuff on the
Microsoft site than Microsoft's search engine. The MS search engine has
improved, but you'll probably find Google will find it there for you.

You can limit your Google search to "Microsoft-related sites" at
http://www.google.com/microsoft.html
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

temporary BackEnd database for large resultset 5
Problems opening workspaces to add tables/fields to secured access 0
Data Type Conversion error 5
Help with Access VBA to create a new file and import textfile data 6
Forward Engineer Visio ER Diagram to MS Access Database 5
temp db 1
Update value of a field 2
Type mismatch error after converting to Access 2007 2

Top