Needed: Code to Import 80+ Tables.

[Sam]

I have about 80+ tables that need to be imported regularly in to a new
database, from an existing database that has 200+ total tables. Using
the manual import and selecting 80+ is a pain. The tables, that need
to be imported start with either X or Y. Could someone please provide
VBA code to import the tables?

Thanks
Sam

 

[John W. Vinson]

I have about 80+ tables that need to be imported regularly in to a new
database, from an existing database that has 200+ total tables. Using
the manual import and selecting 80+ is a pain. The tables, that need
to be imported start with either X or Y. Could someone please provide
VBA code to import the tables?

Thanks
Sam

What I'd suggest to be on the safe side is to create an 80+ row table with the
names of the tables to be imported: tblImportList say, with fields
RemoteTablename and LocalTablename (allowing them to be different). You could
then use code like the following (untested air code):

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset "tblImportList", dbOpenSnapshot
Do Until rs.EOF
DoCmd.TransferDatabase acImport, _
"Microsoft Access", _
"K:\Path\ExistingDatabase.mdb", _
acTable, _
rs!RemoteTablename, _
rs!LocalTablename
rs.MoveNext
Loop

You'll need to flesh this out with error trapping, perhaps also deleting the
local tables prior to importing, etc.; and you will certainly want to
regularly compact the target database!

John W. Vinson [MVP]