Importing all tables from 1 DB into another

[Guest]

Hi there all! I am using Access 2003 and I want to import all tables from one
DB into another at the click of a button. I've used DoCmd.TransferDatabase
acImport before, but there are 40 or 50 tables and I don't want to write a
line of code for every table.

I figure there's an easy way to loop through the table defs in the source DB
and then transfer them in (copying over the existing table) in my source.

What I am doing is allowing my users to copy the data from the source into a
sort of temp DB so they can play around with queries and reports. This way,
they can work with a copy of the live data instead of the actual data.

Any suggestions?

Thanks!!
CH

 

[Douglas J. Steele]

Assuming that you're trying to transfer the tables from your current
database into another one (and assuming that the other database doesn't
already exist), try something like the following untested air-code:

Dim dbCurr As DAO.Database
Dim dbOther As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim strDatabase As String

' Get the name of the new database to create
' (You can use the code at http://www.mvps.org/access/api/api0001.htm
' to allow the user to pick where on the machine to put it...)
strDatabase = "C:\Temp\Test.mdb"
' Create the external database
Set dbOther = CreateDatabase(strDatabase, dbLangGeneral)

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
Debug.Print tdfCurr.Name
DoCmd.TransferDatabase acExport, "Microsoft Access", _
strDatabase, acTable, tdfCurr.Name, _
tdfCurr.Name
End If
Next tdfCurr