TransferDatabase Import

[Red]

If there a way to loop through and import all tables in an ODBC database
when using TransferDatabase.

I need to backup an accounts database (Multisoft) on a monthly basis which
consists of 70+ tables and if possible want to avoid manually coding the
import of each. The manual route can be seen in the code below.

DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=HTA_AA",
acTable, _
"jcjobdet", strShortYear & "/" & txtMonth & "_" & "JCJOBDET"

Really appreciate all help.

 

[Paul Overway]

You would need a table listing all of the tables you want imported.
Assuming that table is named tblImportTables and it has a field named
TABLENAME... you would write code similar to this:

Dim db as database
Dim rst as recordset
Dim strTableName

Set db = currentdb()
Set rst = db.OpenRecordset("tblImportTables")

If not rst.Eof then
rst.MoveFirst

Do Until rst.EOF
DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=HTA_AA", acTable, _
rst.Fields("TABLENAME") , _
strShortYear & "/" & txtMonth & "_" &
rst.Fields("TABLENAME")
rst.MoveNext
Loop
End If