Sometime it can be useful to create a new blank database via code, perhaps to export data, who knows.

Below is a simple skeleton function that enables you to accomplish exactly that.

'---------------------------------------------------------------------------------------
' Procedure : Db_CreateBlankDb
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Creates a new blank Microsoft Access database file
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Microsoft Access XX.0 Object Library
'             Microsoft Office XX.0 Access database engine Object Library
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and file name to give the new database file
' dbFormat  : (optional) file format to be used when creating the file
' sPwd      : (optional) password to encrypt the database with
'
' Usage:
' ~~~~~~
' Db_CreateBlankDb("C:\Users\Dev\Desktop\newDB.accdb", dbVersion150)
'   Returns -> Creates an unencrypted accdb
'
' Db_CreateBlankDb("C:\Users\Dev\Desktop\newDB.accdb", dbVersion150, "123")
'   Returns -> Creates an encrypted accdb with a password of 123
'
' Db_CreateBlankDb("C:\Users\Dev\Desktop\newDB.accdb", dbVersion40)
'   Returns -> Creates an unencrypted mdb
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2021-11-29              Initial Relase
' 2         2024-03-11              Updated copyright
'                                   Added dbFormat argument
'---------------------------------------------------------------------------------------
Public Function Db_CreateBlankDb(ByVal sFile As String, _
                                 Optional dbFormat As DAO.DatabaseTypeEnum, _
                                 Optional ByVal sPwd As String) As Boolean
    On Error GoTo Error_Handler
    Dim oAccess               As Access.Application

    'Check if the db already exists?!  Prompt user?  Delete?

    Set oAccess = New Access.Application

    If dbFormat = 0 Then
        'No format specified
        If sPwd = "" Then
            'No password provided
            oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral
        Else
            'Password provided
            oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral & ";pwd=" & sPwd, dbEncrypt
        End If
    Else
        'Format specified
        If sPwd = "" Then
            'No password provided
            oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbFormat
        Else
            'Password provided
            oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral & ";pwd=" & sPwd, dbFormat + dbEncrypt
        End If
    End If

    Db_CreateBlankDb = True    'if we're here everything worked

Error_Handler_Exit:
    On Error Resume Next
    If Not oAccess Is Nothing Then
        oAccess.Quit
        Set oAccess = Nothing
    End If
    Exit Function

Error_Handler:
    Debug.Print Err.Number, Err.Description
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: Db_CreateBlankDb" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

In an ideal world I would also include an optional argument for the language, but sadly you cannot automate the DAO.LanguageConstants and if you do you will receive the following error:

Thus, in the code above, I am using dbLangGeneral (suitable for English, German, French, Portuguese, Italian, and Modern Spanish) as the language to use which sets the Collating Order accordingly. Of course, feel free to change this to the value that matches your environment/needs!

For anyone exploring the command a little bit here are some fundamentals from testing:

        'On Access 2013
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral    '168KB - Access 2007-2013 (accdb)
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion40 '76KB - Microsoft Jet database engine version 4.0 file format (mdb)
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion30 'Error:  3170 - Could not find installable ISAM.
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion20 'Error:  3170 - Could not find installable ISAM.
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion11 'Error:  3170 - Could not find installable ISAM.
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion10 'Error:  3170 - Could not find installable ISAM.

        'On Access 365
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral    '168KB - Access 2007-2013 (accdb)
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion167  '168KB - Access 2007-2016 (accdb)
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion150  '168KB - Access 2007-2016 (accdb)
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion140  '168KB - Access 2007-2016 (accdb)
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion120  '168KB - Access 2007-2016 (accdb)
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion40 '76KB - Access 2002-2003 - Microsoft Jet database engine version 4.0 file format (mdb)
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion30 'Error:  3170 - Could not find installable ISAM.
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion20 'Error:  3170 - Could not find installable ISAM.
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion11 'Error:  3170 - Could not find installable ISAM.
        oAccess.DBEngine.CreateDatabase sFile, dbLangGeneral, dbVersion10 'Error:  3170 - Could not find installable ISAM.

Also, you can use the database object within the function to perform other operation once it has been created:

• Change properties
• Add references
• etc.

As such, we could modify the above procedure to something like:

'---------------------------------------------------------------------------------------
' Procedure : Db_CreateBlankDb
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Creates a new blank Microsoft Access database file
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Microsoft Access XX.0 Object Library
'             Microsoft Office XX.0 Access database engine Object Library
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and file name to give the new database file
' dbFormat  : (optional) file format to be used when creating the file
' sPwd      : (optional) password to encrypt the database with
'
' Usage:
' ~~~~~~
' Db_CreateBlankDb("C:\Users\Dev\Desktop\newDB.accdb", dbVersion150)
'   Returns -> creates an unencrypted accdb
'
' Db_CreateBlankDb("C:\Users\Dev\Desktop\newDB.accdb", dbVersion150, "123")
'   Returns -> creates an encrypted accdb with a password of 123
'
' Db_CreateBlankDb("C:\Users\Dev\Desktop\newDB.accdb", dbVersion40)
'   Returns -> creates an unencrypted mdb
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2021-11-29              Initial Relase
' 2         2024-03-11              Updated copyright
'                                   Added dbFormat argument
'---------------------------------------------------------------------------------------
Public Function Db_CreateBlankDb(ByVal sFile As String, _
                                 Optional dbFormat As DAO.DatabaseTypeEnum, _
                                 Optional ByVal sPwd As String) As Boolean
    On Error GoTo Error_Handler
    Dim oAccess               As Access.Application
    Dim oDb                   As DAO.Database
    Dim oPrp                  As DAO.Property

    'Check if the db already exists?!  Prompt user?  Delete?

    Set oAccess = New Access.Application

    If dbFormat = 0 Then
        'No format specified
        If sPwd = "" Then
            'No password provided
            Set oDb = oAccess.DBEngine.CreateDatabase(sFile, dbLangGeneral)
        Else
            'Password provided
            Set oDb = oAccess.DBEngine.CreateDatabase(sFile, dbLangGeneral & ";pwd=" & sPwd, dbEncrypt)
        End If
    Else
        'Format specified
        If sPwd = "" Then
            'No password provided
            Set oDb = oAccess.DBEngine.CreateDatabase(sFile, dbLangGeneral, dbFormat)
        Else
            'Password provided
            Set oDb = oAccess.DBEngine.CreateDatabase(sFile, dbLangGeneral & ";pwd=" & sPwd, dbFormat + dbEncrypt)
        End If
    End If
    
    With oDb
        Set oPrp = .CreateProperty("AppTitle", dbText, "New Database Via VBA")
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("Perform Name AutoCorrect", dbLong, 0)
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("Track Name AutoCorrect Info", dbLong, 0)
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("Auto Compact", dbLong, 0) 'Compact on Close
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("DesignWithData", dbLong, 0) 'Enable Layout View
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("UseMDIMode", dbLong, 1) '0=Tabbed Documents / 1=Overlapping Windows
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("AllowDatasheetSchema", dbLong, 0) 'Enable design changes for tables in datasheet view
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("Remove Personal Information", dbLong, 1) 'Remove personal information from file properties on save
        .Properties.Append oPrp
    End With

    Db_CreateBlankDb = True    'if we're here everything worked

Error_Handler_Exit:
    On Error Resume Next
    Set oDb = Nothing
    If Not oAccess Is Nothing Then
        oAccess.Quit
        Set oAccess = Nothing
    End If
    Exit Function

Error_Handler:
    Debug.Print Err.Number, Err.Description
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: Db_CreateBlankDb" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

where we now setup all sort of database properties and you can go even further, create tables, relationships, …

One last more advanced example to demonstrate a bit more of this to inspire yourself with

'---------------------------------------------------------------------------------------
' Procedure : Db_CreateBlankDb
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Creates a new blank Microsoft Access database file
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Microsoft Access XX.0 Object Library
'             Microsoft Office XX.0 Access database engine Object Library
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and file name to give the new database file
' dbFormat  : (optional) file format to be used when creating the file
' sPwd      : (optional) password to encrypt the database with
'
' Usage:
' ~~~~~~
' Db_CreateBlankDb(application.CurrentProject.Path & "\newDB.accdb", dbVersion150)
' Db_CreateBlankDb("C:\Users\Dev\Desktop\newDB.accdb", dbVersion150)
'   Returns -> creates an unencrypted accdb
'
' Db_CreateBlankDb("C:\Users\Dev\Desktop\newDB.accdb", dbVersion150, "123")
'   Returns -> creates an encrypted accdb with a password of 123
'
' Db_CreateBlankDb("C:\Users\Dev\Desktop\newDB.accdb", dbVersion40)
'   Returns -> creates an unencrypted mdb
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2021-11-29              Initial Relase
' 2         2024-03-11              Updated copyright
'                                   Added dbFormat argument
'---------------------------------------------------------------------------------------
Public Function Db_CreateBlankDb(ByVal sFile As String, _
                                 Optional dbFormat As DAO.DatabaseTypeEnum, _
                                 Optional ByVal sPwd As String) As Boolean
    On Error GoTo Error_Handler
    Dim oAccess               As Access.Application
    Dim oDb                   As DAO.Database
    Dim oPrp                  As DAO.Property
    Dim sSQL                  As String

    'Check if the db already exists?!  Prompt user?  Delete?


    Set oAccess = New Access.Application
    '**********************************************************************
    ' Database Creation
    '**********************************************************************
    If dbFormat = 0 Then
        'No format specified
        If sPwd = "" Then
            'No password provided
            Set oDb = oAccess.DBEngine.CreateDatabase(sFile, dbLangGeneral)
        Else
            'Password provided
            Set oDb = oAccess.DBEngine.CreateDatabase(sFile, dbLangGeneral & ";pwd=" & sPwd, dbEncrypt)
        End If
    Else
        'Format specified
        If sPwd = "" Then
            'No password provided
            Set oDb = oAccess.DBEngine.CreateDatabase(sFile, dbLangGeneral, dbFormat)
        Else
            'Password provided
            Set oDb = oAccess.DBEngine.CreateDatabase(sFile, dbLangGeneral & ";pwd=" & sPwd, dbFormat + dbEncrypt)
        End If
    End If

    '**********************************************************************
    ' Set some database properties
    '**********************************************************************
    With oDb
        Set oPrp = .CreateProperty("AppTitle", dbText, "New Database Via VBA")
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("Perform Name AutoCorrect", dbLong, 0)
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("Track Name AutoCorrect Info", dbLong, 0)
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("Auto Compact", dbLong, 0)    'Compact on Close
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("DesignWithData", dbLong, 0)    'Enable Layout View
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("UseMDIMode", dbLong, 1)    '0=Tabbed Documents / 1=Overlapping Windows
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("AllowDatasheetSchema", dbLong, 0)    'Enable design changes for tables in datasheet view
        .Properties.Append oPrp
        Set oPrp = .CreateProperty("Remove Personal Information", dbLong, 1)    'Remove personal information from file properties on save
        .Properties.Append oPrp
    End With

    '**********************************************************************
    ' Create a Table - There are different ways this can be done
    '**********************************************************************
    sSQL = "CREATE TABLE tbl_DB_Object_Password (" & vbCrLf & _
           "     ObjectPasswordId              COUNTER PRIMARY KEY," & vbCrLf & _
           "     ObjectType      TEXT(10) NOT NULL," & vbCrLf & _
           "     ObjectName      TEXT(255) NOT NULL," & vbCrLf & _
           "     ObjectPassword      TEXT(255) NOT NULL," & vbCrLf & _
           "     CONSTRAINT UniqueObjectEntry UNIQUE (ObjectType, ObjectName)" & vbCrLf & _
           ");"
    oDb.Execute sSQL, dbFailOnError
    
    '**********************************************************************
    ' Add some values to the table
    '**********************************************************************
    sSQL = "INSERT INTO tbl_DB_Object_Password (ObjectType, ObjectName, ObjectPassword) VALUES ('Form', 'Form1', '123');"
    oDb.Execute sSQL, dbFailOnError
    sSQL = "INSERT INTO tbl_DB_Object_Password (ObjectType, ObjectName, ObjectPassword) VALUES ('Report', 'Report1', 'abc');"
    oDb.Execute sSQL, dbFailOnError
    
    '**********************************************************************
    ' Create a Query
    '**********************************************************************
    sSQL = "SELECT TOP 1 * FROM tbl_DB_Object_Password ORDER BY ObjectName;"
    Call oDb.CreateQueryDef("First Password Entry", sSQL)
    

    Db_CreateBlankDb = True    'if we're here everything worked

Error_Handler_Exit:
    On Error Resume Next
    Set oDb = Nothing
    If Not oAccess Is Nothing Then
        oAccess.Quit
        Set oAccess = Nothing
    End If
    Exit Function

Error_Handler:
    Debug.Print Err.Number, Err.Description
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: Db_CreateBlankDb" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Keep Things Simple (KISS)

I know the point of this article was to show how we can create databases via VBA automation. That said, don’t forget that you can easily create a blank database and set it up the way you want it manually, one time, and then store it in a folder (perhaps with the database itself) and then merely copy it whenever you need a new blank db.

Another option would be to store it directly in the database as an attachment (I can’t believe “I” am suggesting this!) and extract a copy whenever you need a new blank db.

So lots of possible approaches are available. Don’t get tunnel vision and only focus on VBA, sometimes there may be easier approaches that make sense.
 

Useful Links

DBEngine.CreateDatabase method (DAO)

Creates a new Database object, saves the database to disk, and returns an opened Database object (Microsoft Access workspaces only).

Page History