DataWright Information Services

Consulting and Resources for Excel and Access

Create an Access database and table Using ADOX

This is Part 1 of 7 in a tutorial on using code to integrate Excel and Access.

In the sample file, Population projections has actual populations by country for 1950 and 2000, with forecasts going out to 2050. By clicking the Create Database button, you will create an Access database called Populations.mdb in the same folder as the Excel file. It contains a single table, tblPopulation, with seven fields. This database will be used for the remainder of the tutorial, so creating it is the first step in the process.

Note:

  1. Because all of the code for this tutorial exists in a single module, I have placed a constant at the top of the module that defines the name of the database. This removes the need to keep redefining it for each procedure, and means that you only need to change one place in the code to refer to a different database.
  2. To use this code in your own file you will need to set the appropriate references. In the code environment go to Tools > References and select Microsoft ActiveX Data Objects 2.x Library (the highest version available on your system) and Microsoft ADO Ext. 2.x for DDL And Security (version 6.0 if you are using Excel 2007). Not all installations of Office have the ADOX libraries. If you can't find them on your PC, you will need to download the pre-created sample from the link above. Place it in the same folder as the Excel file that you downloaded.

The constant is shown below. It needs to go before the first Sub procedure in the module:

Const TARGET_DB = "DB_test1.mdb"

Now for the first procedure, which runs when you click Create Database on the Population Projections sheet:

Sub CreateDB_And_Table()

    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim sDB_Path As String

    sDB_Path = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB

    'delete the DB if it already exists
    On Error Resume Next
    Kill sDB_Path
    On Error GoTo 0

    'create the new database
    Set cat = New ADOX.Catalog
    cat.Create _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & sDB_Path & ";"

    'create the table
    Set tbl = New ADOX.Table
    tbl.Name = "tblPopulation"

    'create the fields for the new table
    tbl.Columns.Append "PopID", adInteger
    tbl.Columns.Append "Country", adVarWChar, 60
    tbl.Columns.Append "Yr_1950", adDouble
    tbl.Columns.Append "Yr_2000", adDouble
    tbl.Columns.Append "Yr_2015", adDouble
    tbl.Columns.Append "Yr_2025", adDouble
    tbl.Columns.Append "Yr_2050", adDouble

    'append the newly defined table to the Tables collection in the database
    cat.Tables.Append tbl

    'Clean up references
    Set cat = Nothing

End Sub

Note the order in which this happens.

  • Create a new Catalog (which is how you refer to a database with ADOX)
  • Create a new table
  • Append the new fields to that table
  • Append the new table to the Tables collection in the database.

What is ADOX?

ADOX is an extension to ADO that lets you create and modify database structures (tables and fields). It was created specifically for working with the Jet database engine. According to Microsoft, you might have problems using it with other database engines.

However, it is useful for creating tables and fields because you can easily define data types and indexes (which you cannot do in ADO).

Next: Part 2>>