Access: Create new database file and transfer tables using VBA in Access 2003/XP/2000/97

Question: In 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.