---

MS Access 2003: Create new database file and transfer tables using VBA

This MSAccess tutorial explains how to create a new database file and transfer tables using VBA code in Access 2003 (with screenshots and step-by-step instructions).

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.