TC said:
TransferDatabase. See F1 help.
HTH,
TC (MVP Access)
http://tc2.atspace.com
I found it I think. It is not easy understanding help in Office sometimes.
I am for ever learning.
Access: Create new database file and transfer tables using VBA
Question: I have an Access database with lookup tables as well as data
entry tables. I'd like to have a button which allows the user to start a
new database file with the lookup tables containing the data, but with
empty data entry tables.
Essentially, I want to be able to save the data from the original database
and start a new database (file) with all of the lookup tables intact, but a
clean set of data entry tables.
Answer: You can create a new mdb file using the following VBA code:
Sub CreateNewMDBFile()
Dim ws As Workspace
Dim db As Database
Dim LFilename As String
'Get default Workspace
Set ws = DBEngine.Workspaces(0)
'Path and file name for new mdb file
LFilename = "c:\NewDB.mdb"
'Make sure there isn't already a file with the name of the new database
If Dir(LFilename) <> "" Then Kill LFilename
'Create a new mdb file
Set db = ws.CreateDatabase(LFilename, dbLangGeneral)
'For lookup tables, export both table definition and data to new mdb
file
DoCmd.TransferDatabase acExport, "Microsoft Access", LFilename,
acTable, "Lookup Table1", "Lookup Table1", False
'For data entry tables, export only table definition to new mdb file
DoCmd.TransferDatabase acExport, "Microsoft Access", LFilename,
acTable, "DataEntry Table1", "DataEntry Table1", True
db.Close
Set db = Nothing
End Sub
This subroutine creates an mdb file called c:\NewDB.mdb and exports two
tables from the original mdb file:
Lookup Table1 is exported from the original mdb file to the new file with
both the table definition and data intact.
DataEntry Table1 is exported from the original mdb file to the new file with
only the table definition. No records are transferred.
