Create an Access database and table Using ADOX

Download the
.mdb sample file (68,765 bytes)
Download the .accdb sample file (71,432 bytes)
If you can’t create the database,
download this one (8558
bytes)

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:

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

DataWright Information Services

Consulting and Resources for Excel and Access

Create an Access database and table Using ADOX

Create a Primary Key for the new table

What is ADOX?