For Each in another database

  • Thread starter Thread starter Katrina
  • Start date Start date
K

Katrina

I want to run a command like

For Each objtable In CurrentData.AllTables

Except instead of Current data, I want to specify another
database.

I am trying to import all the tables from one database to
the other. I dont want to do an export.

Any suggestions?
 
Katrina said:
I want to run a command like

For Each objtable In CurrentData.AllTables

Except instead of Current data, I want to specify another
database.

I am trying to import all the tables from one database to
the other. I dont want to do an export.

Any suggestions?
Click to expand...

If the other database is a Jet database (i.e., an .mdb file), then you
could use the DAO TableDefs collection instead. Code might look
something like this:

Dim dbSource As DAO.Database
Dim tdf As DAO.TableDef
Dim strSourceDB As String
Dim strTableName As String

strSourceDB = "C:\Path To\SourceDB.mdb"
Set dbSource = DBEngine.OpenDatabase(strSourceDB)

For Each tdf In dbSource.TableDefs

strTableName = tdf.Name

If Left(strTableName, 4) <> "MSys" Then

' Not a system table, so import it.
DoCmd.TransferDatabase _
acImport, _
"Microsoft Access", _
strSourceDB, _
acTable, _
strTableName, _
strTableName, _
False

End If

Next tdf

dbSource.Close
Set dbSource = Nothing
 
Back
Top