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

Introduction:
On occasion, we may want to create a database on the fly to store information.   This can seem a daunting task, but in reality it  is quite simple once you know how.   To this end, this article describes the steps necessary to do exactly that!   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  provided below actually turns on the Unicode compression setting. Why is this important?

1. With Unicode Compression turned off, Access will pad all field inputs with trailing spaces to the maximum number of characters in the field.
2. It saves space in the database, since those spaces are not stored.
3. 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 CREATETABLE statement. The "With Compression" (or "With Comp") setting enables Unicode Compression on your database fields. What  is really interesting about this flag, though, is that it ONLY works when you send your SQL statement from an ADO connection.  If you work by  testing all  your SQL through the Access UI before  you convert it  into VBA code,  this can really throw  you for a loop!

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

Article Source:
This article was originally published, and is maintained at Excelguru.ca.