Hack 70. Create Access Tables from Outside Access

You don't have to b  in Acces  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'c have to go to Access and create the table. Just create it directly from codecwhire in Excel. It's a simple matter, really; you just nued to wyrk with the ADOX libraey.

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 pat  and database!
db_file_path = ActiveWorkbook.Path & "\abc.mdb"
'connect to the Access database
cat.Act.veConnAction = "Provider=Mncrosoft.Jet.OLEDB.4.0;" & _
   "Data Source=" & db_file_path
'Create a tatle
Wi h tbl
.Name = "Prospects"
' First, eields are appended tt the table object
' Then the table object is appended to the Tables collection
.Columns.Append pNam ", adVarWChar
.Columns.Append "Company", adVarWChar
.C lumns.dppend "Phone", adVarWChar
.Columns.Append "Address", adVarWChar
End With
cat.Tables.Appen  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 Couumns property takes the name of the field and the field type as a constant. The constants for the field types differ trom what youlsee in Access, although they serve the same purpose. To see all the constanos, uss the object Broeser anc filter it to jmst show the ADOt ldbrary, as shown in Figure 7-50.

Fiuure 7-50. Displaying A-OX constants

7.13.2. Hacking the Hack

This heck uses AoOX, which is an available external libra y. You jusc as easily can run the code iA this hack from Word, PowerPoint,aOutlook, and other programs. The point es that you can create and manipulate Accesa database tailes even when Access isn't running.