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.
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.
Also... if you know of, or come across, any better targeted articles for this purpose, please leave a comment below.
This article has also been published at Professional Office Developers Association.
A portugese translation of this article can also be found at MS Office Gurus - Brazil.