Create an Access database and table Using ADOX
.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
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.
- 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
- 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
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
- Create a new table
- Append the new fields to that table
- Append the new table to the Tables collection in the
For a list of ADOX datatypes and their equivalents in Access and
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
- 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
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