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

    'now create the primary key: added 06 Oct 2010
    Call CreatePrimaryKey("tblPopulation", "PopID")

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.

For a list of ADOX datatypes and their equivalents in Access and SQL, see this page.

Create a Primary Key for the new table

This section has been added to demonstrate how to create a primary key. The steps involved are:

  • Connect to the existing database
  • Create the new index (deleting any existing primary keys first)
  • Append column(s) to the index
  • Append the index to the Indexes collection

The code for this procedure, which is called by the one that creates the database and table, is shown below. Once the code has run, check the table design and you will see the primary key. 

Private Sub CreatePrimaryKey(strTableName As String, _
        varPKColumn As Variant)
    Dim cnn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim idx As ADOX.Index
    Dim sDB_Path As String
    Dim MyConn
    
    sDB_Path = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB
    
    
    'create a connection to the existing database
    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
    
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With
    
    'create the catalog and use the newly created connection
    'also set the table reference
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = cnn
    
    Set tbl = cat.Tables(strTableName)
    
    'delete any existing primary keys
    For Each idx In tbl.Indexes
        If idx.PrimaryKey Then
            tbl.Indexes.Delete idx.Name
        End If
    Next idx
    
    'create a new primary key
    Set idx = New ADOX.Index
    With idx
        .PrimaryKey = True
        .Name = "PrimaryKey"
        .Unique = True
    End With
    
    'append the column
    idx.Columns.Append varPKColumn
    
    'append the index to the collection
    tbl.Indexes.Append idx
    tbl.Indexes.Refresh
    
    'clean up references
    Set cnn = Nothing
    Set cat = Nothing
    Set tbl = Nothing
    Set idx = Nothing
    
End Sub

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