Bet you'll never solve this 1 - VBA compact and resume macro?

[wrek]

I have an interesting conundrum on my hands...

What I'm trying to do: Through VBA, archive data in ODBC linked tables, into static tables (a one-time snapshot kind of deal).

How:

(1) In my Archive_Master database, looping through the tables, and for each ODBC linked table (T)
(2) Running SQL Make-Table to create Table (T1)
(3) Creating a new database (T1_db) with the same name of the table, (if database doesn't already exist)*
(4) Transferring the newly made static table (T1) to (T1_db)
(5) Deleting (T1) from Archive_Master db.
(6) Loop back to (1)

*reason I need to send the tables to their own databases, is some of them are very large and result in .mdbs over 1 GB

The problem:

This method works well. BUT...

After about 50 or so tables, Archive_Master balloons (obviously) to being above 2 GB, upon which I get the usual errors because the db's size is inflated - this stops the macro.

I'm aware of the challenge compacting the current, open database through VBA (and Compact on close won't work as that will end my macro).

How can I compact this database in code, and allow my code to resume enumerating the tables and archiving them?

tx...a fair maidens hand in marriage and keys to the kingdom in reward

 

[ghudson]

I think this is true but I have never had the need to do it...

If you tranfer the tables to a new db, the compact step will not be needed since Access will take care of that during the table transfer.

Maybe this will give you another idea on how to do what you want...
Export All Database Objects Into Text Files

 

[wrek]

1) Transferdatabase compacts the newly created db quite fine, it's the existing (Master) db, that balloons.

Just for my own edification - instead of the Transferdb, is there a way to create an object directly in another database? (recordset maybe?) or use Transferdb more efficiently? Currently, I'm calling:


If DB doesnt exist
DoCmd.RunSQL (strQry) 'maketable
CreateNewMDB(strDB, dbVersion40) 'function to create db
DoCmd.TransferDatabase acExport, "Microsoft Access", strDB, acTable, newstrTBL, strTable
DoCmd.DeleteObject acTable, newstrTBL
End If


2) Your code is a very good solution. Thanks for posting it. Backs the data up faster and using less space.

(Just one note: For the sake of us less savvy VBA'ers, you might want to add to your sample that to import a .txt file into a Table object, you've got to use the TransferText function as opposed to the LoadFromText which works fine for all other object types)

tx again.