Creating an Access Database (on the fly) Using VBA and SQL

I recently had reason to create a database on the fly if one did not exist. Since it took me some trial, error and searching (and then more trial and error,) I decided to share the method to do this. The following routine will create an Access database from any VBA enabled application, such as Word, Excel, Outlook, etc...

About the Example:

The example below creates a database at the root of the C: drive, using your MS Office Username. It also creates a new table "tblSample" with six fields in it. The most interesting part about this is that the code I provided below actually turns on the Unicode compression setting. Why is this important?

• With Unicode Compression turned off, Access will pad all field inputs with trailing spaces to the maximum number of characters in the field.
• It saves space in the database, since those spaces are not stored.
• Without Unicode compression turned on, you will probably need to Trim (remove spaces) from all field values you work with in code.

Unicode compression is, by default, turned on when you create a table through the UI in Access. It is by default turned OFF though, when you create a table using SQL's CREATE TABLE statement. The "With Compression" (or "With Comp") setting enables Unicode Compression on your database fields. What I found really interesting about this flag, though, is that it ONLY works when you send your SQL statement from an ADO connection. I prefer to test all my SQL through the Access UI before I pull it into VBA code, so this really threw me for a loop for quite a while.

Code Required:

The code below goes in a standard module. Don't forget to set a reference to the Microsoft ActiveX Data Objects Library. The following code was developed using the 2.8 version.

NOTE: There is no error handling in this routine. Running it more than once will result in an error about the database already being created.

Private Sub CreateDatabase()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Create an Access database on the fly
    Dim dbConnectStr As String
    Dim Catalog As Object
    Dim cnt As ADODB.Connection
    Dim dbPath As String

    'Set database name here
    dbPath = "C:" & Application.UserName & ".mdb"
    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

    'Create new database
    Set Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
    Set Catalog = Nothing

    'Connect to database and insert a new table
    Set cnt = New ADODB.Connection
    With cnt
        .Open dbConnectStr
        .Execute "CREATE TABLE tblSample ([Name] text(50) WITH Compression, " & _
                 "[Address] text(150) WITH Compression, " & _
                 "[City] text(50) WITH Compression, " & _
                 "[ProvinceState] text(2) WITH Compression, " & _
                 "[Postal] text(6) WITH Compression, " & _
                 "[Account] decimal(6))"
    End With
    Set cnt = Nothing
End Sub

Additional Info:

One challenge with creating Access tables via ADO is that the data types are not named consistently between Access and ADO. If you are trying to create a table via ADO and SQL, you may want to check out this article for a bit of help.