Access Hacks

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

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