MS Access: Create new database file and transfer tables using VBA in Access 2003/XP/2000/97
Question: In Microsoft Access 2003/XP/2000/97, I have a 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.