Hack 70. Create Access Tables from Outside Access

You don't have to be in Access to use Access.

Here's the scenario: you have an Excel solution that needs to populate an Access table. The table will be a new table in the database. You might think the table needs to exist before you can populate it via ADO or some other means, so you manually create the table and then go back to Excel and run the routine that populates the table.

Actually, you don't have to go to Access and create the table. Just create it directly from code while in Excel. It's a simple matter, really; you just need to work with the ADOX library.

In Excel, set a reference to ADOX by using the Tools References menu and setting the references in the References dialog box, as shown in Figure 7-49.

Figure 7-49. Setting a reference to ADOX

7.13.1. The Code

It's now just a matter of whipping up a little code that uses the ADOX programmatic model. In an Excel code module, enter this code:

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim db_file_path As String
'change path and database!
db_file_path = ActiveWorkbook.Path & "\abc.mdb"
'connect to the Access database
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=" & db_file_path

'Create a table
With tbl
.Name = "Prospects"
' First, fields are appended to the table object
' Then the table object is appended to the Tables collection
.Columns.Append "Name", adVarWChar
.Columns.Append "Company", adVarWChar
.Columns.Append "Phone", adVarWChar
.Columns.Append "Address", adVarWChar
End With

cat.Tables.Append tbl

Set cat = Nothing
MsgBox "Table created!"

Just be sure to change the hardcoded database name and path. This code creates the Prospects table in the Access database. The table will have four fields. The Append method of the Columns property takes the name of the field and the field type as a constant. The constants for the field types differ from what you see in Access, although they serve the same purpose. To see all the constants, use the Object Browser and filter it to just show the ADOX library, as shown in Figure 7-50.

Figure 7-50. Displaying ADOX constants

7.13.2. Hacking the Hack

This hack uses ADOX, which is an available external library. You just as easily can run the code in this hack from Word, PowerPoint, Outlook, and other programs. The point is that you can create and manipulate Access database tables even when Access isn't running.