how to INSERT INTO a table in ANOTHER mdb file 1

[teach314]

I regularly receive mdb files (each holding 200 tables) that require processing. My code then loops through each Table, doing the following...
a) Copy and Rename a BLANK TEMPLATE table, b) run SQL to process the table data, c) use SQL to INSERT INTO the table renamed in a). All of the code works perfectly.

My problem has to do with the mdb file getting too big (~2 GB). I can't delete the original tables and compact to free space as I go, because the original tables are to be archived, as are the newly processed tables.

Here is my question: For each table being processed, is there a way to change step c) above to save the data directly into ANOTHER mdb file. I don't want to produce the table in the original mdb file then TRANSFER it to the new mdb file, because that still leaves me having problems with file bloat. Can I INSERT INTO a table in another mdb file DIRECTLY?

Thanks for any clues as to how to proceed.

 

[dhookom]

Access SQL supports syntax like

INSERT INTO [;Database=C:\PathToDb\YourDbName.mdb].[YourTableName] (
[LastName] )
Values( 'Jones')

Duane
Hook'D on Access
MS Access MVP

 

[teach314]

that worked really well. Because the INSERT INTO query involved multiple records, I had to use the external database info in an IN clause instead of where you showed it. Thanks for the tip. there's several other places I can use this idea too.