Creating an Access Database (on the fly) Using VBA and SQL
Posted by Ken Puls, CMA, Microsoft MVP (Excel) on July 4th, 2007
Introduction:
On occassion, 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?
- 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 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.
'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.