temp database 1

[mpastore]

Does anyone have somewhat generic code to create a temp database and put temp tables there via a link to the current database? I am using temp tables in my front end but am sick of dealing wih bloat. Thanks

Mike Pastore

Hats off to (Roy) Harper

 

[scriverb]

Here is some VBA code to create a new TempTables database in the location that you indicate in the red area of the code:

Dim wrkDefault As Workspace
Dim dbsNew As Database
Set wrkDefault = DBEngine.Workspaces(0)
' Make sure there isn't already a file with the name of
' the new database.
If Dir(&quot;F:\ACCESS\MISC\TempTables.mdb&quot <> &quot;&quot; Then Kill &quot;TempTables.mdb&quot;
' Create a new database with the specified
' collating order.
Set dbsNew = wrkDefault.CreateDatabase(&quot;F:\ACCESS\MISC\TempTables.mdb&quot;, dbLangGeneral)

Now just what is it that you want to do with the linking. Is it that you want to create the links after you create the temp tables on the fly? Just let me know I can help.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[mpastore]

Yes, I want to create temp tables in the linked db so that I can fill them to fullfill requirments of complex reports.

Mike Pastore

Hats off to (Roy) Harper

 

[scriverb]

Mike, How is it that you are going to create these temp tables. Is there a specific structure that you will want to use? I mean do you have a table currently in your FE database that can be cloned or is this a new structure that you will want to create each time have the need?

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[mpastore]

Bob,

I think the way I would handle it is that I would have the temp table in my front-end db named something like _temp_001_stru (0 records) and I would copy the structure to the temp .mdb file and name it something like _temp_001.

I like keeping the structure in my front-end because it has a lot of fields and I would not like to use DDL to create it via code. Hope this helps. Mike

Mike Pastore

Hats off to (Roy) Harper

 

[scriverb]

If you are going to have a table in your FE to be cloned then you can create a SAVED query in the FE like this one and execute it one time after the database is created with the above code:

SELECT * INTO [_temp_001] IN 'F:\ACCESS\MISC\TempTables.mdb'
FROM [_temp_001_stru ];

Save as qryMT_TempTable and then execute with:

Dim db as DAO.Database
Dim TD As DAO.TableDef
Set db = CurrentDb
DoCmd.SetWarnings false
DoCmd.OpenQuery &quot;qryMT_TempTable&quot;
DoCmd.SetWarnings True

Set TD = db.CreateTableDef(&quot;_temp_001&quot
TD.Connect = &quot;;Database=F:\ACCESS\MISC\TempTables.mdb&quot;
TD.SourceTableName = &quot;_temp_001&quot;
MyDB.TableDefs.Append TD
db.close

You will have to run this code one and only once to create your db, temptable, and links to the table. Then bypass after that.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[mpastore]

Thanks, Bob, that will save me time. Mike

Mike Pastore

Hats off to (Roy) Harper

 

[scriverb]

Let me know if you need any more help with this.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[JeremyNYC]

I just implemented something similar to this. The way I did it was to have a file called TempDBStructure.mdb. I just used filecopy to make a dupe of it called TempDB.mdb when I needed it.

I'm sure Bob's method is fine, too, I just thought I'd throw you another option.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.