Temp DBs

[JimS]

OK, I'm sick of bloating my db every time I do external imports. Here's what
I do:

Remove all records from the permanent import table in the db (delete *)
Import from excel or csv to a temp table in the db.
Massage the data and transfer to the permanent import table in the db.
Delete the temp table (Drop)
Use the permanent import table to check integrity of the data, and update
permanent internal tables.
Use the permanent import table from time to time to do further integrity
checks.

This bloats the hell out of the db. In a single (multiple-entry) import
session, it can add 50% to the db, bringing it up over a gig in size. I can
then compact and repair it to as little as 400 MB.

So, I thought I'd go the temp DB strategy. What would you do, step-by-step
for this? Most of the imports are done using the transfertext and
transferspreadsheet commands. The updates, appends, integrity checks, etc,
are standard stored queries.

 

[Jack Leach]

Tony has an excellent solution for doing just this.

http://www.granite.ab.ca/access/temptables.htm

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Dirk Goldgar]

So, I thought I'd go the temp DB strategy. What would you do, step-by-step
for this? Most of the imports are done using the transfertext and
transferspreadsheet commands. The updates, appends, integrity checks, etc,
are standard stored queries.

Following up on Jack's pointer to Tony's TempTables code, I have wraped
similar code in a class module that I use to manage temporary databases and
tables. Class clsWorkDB creates a temporary work-database and allows the
user to create, link to, and manipulate temporary tables in the work
database. A new work database is created for each instance of the class.
All tables are unlinked and the work database is deleted when that instance
is destroyed.

I've posted the class code in the past; if you're interested, I'll post it
again here.