Copy tables between databases HELP Please 1

[kiwuji]

Hi everyone,
I've been stuck with this problem all day, may be someone can help
Scenerio:
1. I have three database A, B, C
-- A is on my hard-drive
-- B is the production database
-- C is the backup of the production database
2. database A feeds database B by importing tables
3. every month the tables in C are replace with the lastest used table from B (copy tables from B to C before feeding B)

Problem:
I have been working on automating this process using VB codes, but i m yet to figure out how to copy tables from B to C without opening the B or C database. I was hoping this codes would do but nothing happens when i tried...see below
My last resort is to do filesystem.copy B, C but these database are so big that it takes about 30 minutes to copy the entire database. SO, PLEASE HELP!!!!!

kiwuji

Trial 1
DBEngine.OpenDatabase(DestDBName, , False).CreateTableDef Table1, , OpenDatabase(sourceDBName).OpenRecordset(Table1,dbOpenTable)

DBEngine.OpenDatabase(DestDBName, , False).CreateTableDef Table2, , OpenDatabase(sourceDBName).OpenRecordset(Table2, dbOpenTable)

Trail 2
DBEngine.OpenDatabase(DestDBName, , False).CreateTableDef Table1, , sourceTable1, sourceDBName

 

[jrbarnett]

Look up DoCmd.TransferDatabase in the help.

John

 

[kiwuji]

Thank you so much jrbarnett...it works

kiwuji

 

[PassingBy]

Another option is to have a 4th database to store copy tables for backups. I use the following example for multiple table exports each month:-

DoCmd.CopyObject "\\Filepath\TableLibrary.mdb", "tblSAPResultMainTableXtab copy as at " & [TxtShortDate], acTable, "tblSAPResultMainTable"

n.b [txtShortDate] is a date provided by the user to add as an extension to the backedup table but you could use something else accordingly.

Martin