VBA code for copying multiple tables from one database to another?

[jane30]

Hi, how can I copy multiple tables in 3 different database to another 3 different database in VBA code?

Thanks alot.

 

[Ninabunny]

What kind of databases and are they going into an access or what?

 

[rewdee]

Hi Jane30

Wouldn't it be easier if you just linked or made reference to the databases and tables rather than trying to make dynamic imports?

If you must do something like the importing from different sources into different databases how about the code below:

Public Sub TransferTables()

Dim Db As Database, db2 As Database, db3 As Database
Dim i As Integer

Set Db = CurrentDb
Set db2 = OpenDatabase("C:\DB2&quot
Set db3 = OpenDatabase("C:\DB3&quot

'Example of adding a table from one database into another
Db.Execute "SELECT Table1.* INTO Table1 IN 'D:\AnotherDb.mdb' FROM Table1;"

'Example of adding a table from another db into a third db
db2.Execute "SELECT Table1.* INTO Table1 IN 'C:\DB3' FROM Table1;"

Set Db = Nothing
Set db2 = Nothing
Set db3 = Nothing

End Sub

I have not tried this but I think it will work so hopefully it helps,

Rewdee

 

[jane30]

Yes, it's transferring tables from one access97 database to another. I want to run this VBA code in MainDB to transfer multiple tables in DB1,DB2,DB3 to DB1a, DB2a, DB3a. This setup is a temporary setup. So, it might not need to link the tables. Basically, this setup will be used only when the tables in DB1a, DB2a, DB3a need to be updated.

Thank you, Reywee. I'll give it try.