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.
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
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.