Published: Wednesday, January 31, 2001
Working with ADOX
By Ramesh Balaji
and Scott Mitchell
Introduction
ADO, as you likely know, is used to access databases (or more generic data stores).
The work that we normally perform with ADO when working with database can be
categorized into the following two activities:
ADO is primarily used for Data Manipulation like Inserting, Updating, Deleting and Querying
data. As far as defining data, like Creating Tables, Indexes, Users, Groups we normally
create SQL Statements and run those queries using ADO Connection and Command Objects.
(For an example see: How do you
create a database table through a SQL statement (via an ASP page, perhaps)?)
There are no objects available in ADO to perform these schema related activities like Table
creation or User creation etc.
With the release of ADO 2.1, Microsoft provided a new set of ADO objects to perform schema
related activities: ADOX, which stands for ADO Extensions. ADOX is an object model that
provides a set of objects that can be used perform activities pertaining to Data Definition.
If you do not have ADO 2.1 or later, you can download the latest version of ADO (for free)
from: http://www.microsoft.com/data/download.htm.
Simply download the latest MDAC SDK.
For the rest of this article we will be examining some examples of using ADOX to create
an Access database, a database table, and a query. Before you delve into the
examples you may wish to familiarize yourself with the ADOX object model. The following
links are great places to start:
The ADOX object model starts with the Catalog object, which is the top-level
object. The Catalog object contains a number of objects as properties, each of which are
collections. (For example, the Tables property of the Catalog object
is a collection of Table objects.) These Catalog object properties
include:
Tables
Groups
Users
Procedures
Views
The Catalog object represents the top level object, schema information about a
data store. In this article we will be examining using ADOX with an Access database
(.MDB file).
Creating a Catalog
For our first example, let's create a new Access database. We can do this via the Create
method of the Catalog object. This will, literally, create an Access database,
generating a .MDB file. We will start by declaring instantiating a Catalog
object.
Dim objADOXDatabase
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")
|
Next we'll use the Create method of the Catalog object to specify
the location of the Database and the appropriate OLEDB provider of the Datasource as the
parameters .
objADOXDatabase.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\customer.mdb"
|
The Catalog is now successfully created. This will create an actual
Access database file, customer.mdb in the C:\ directory. You can,
of course, change the name and directory location by modifying the Data Source
parameter in the OLE DB connection string. Also, note that if a database with the name you
specify already exists you will receive the following error: Microsoft JET Database Engine error '80040e17':
Database already exists.
Creating a Table and Specifying a Primary Key
At this point we have created the Access database file, so let us go ahead and create a
database table. This is done using the Table object.
Dim objFirstTable
Set objFirstTable = Server.CreateObject("ADOX.Table")
objFirstTable.Name = "NewADOX"
objFirstTable.Columns.Append "CustID", adInteger
objFirstTable.Columns.Append "CustName", adVarWChar, 30
objFirstTable.Keys.Append "PK_CustID", 1, "CustID"
|
The above code starts by creating an instance of the Table object. Next,
we give the table a name (NewADOX) and add two columns to it. The first column,
CustID is of type integer. The next column, CustName is a 30
character long wide-varchar. (When using Access 2000, you will need to use the unicode
adWChar and adVarWChar as opposed to the adChar and
adVarChar data types.) These constants, adInteger and adVarWChar,
are defined in adovbs.inc, which will need to be included in your ASP pages either
through a server-side include or through the METADATA tag. For more information on adovbs.inc
be sure to read: adovbs.inc - Use It!.
Next, we create a primary key constraint on the CustID column. This is done via
the Keys object's Append method. The first parameter is the
name of the key, the second parameter specifies what type of key (1 == Primary Key) and the
third parameter specifies what column to place the key on (in this case, CustID).
After specifying all the parameters necessary for the table, we still need to add the table
to our database, customer.mdb. We do this via the Append method of
the Tables property of the Catalog object:
objADOXDatabase.Tables.Append objFirstTable
|
Pretty neat, eh? We've programmatically created an Access database and appended to it a
database table with two columns and a primary key constraint! All using ADOX's object-based
model instead of cryptic SQL commands. The figure to the right illustrates the NewADOX
table structure when viewed through Access 2000. Remember, this table was created programmatically
by our ASP script using ADOX, not through the Access interface.
Now that we've examined how to create an Access database and database table, let's look at
how we can iterate through the tables of a database using ADOX. We'll look at this in
Part 2 of this article. Before we do, though, take
a moment to examine the complete source code of the ASP page we dissected earlier. (Recall
that this script created both an Access database file and a table (NewADOX with
two columns...)
Read Part 2!
Source Code
<% Option Explicit %>
<!--#include file="adovbs.inc"-->
<%
Dim objADOXDatabase
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")
'Create the database file
objADOXDatabase.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\customer.mdb"
'Create a database table and add columns/PK
Dim objFirstTable
Set objFirstTable = Server.CreateObject("ADOX.Table")
objFirstTable.Name = "NewADOX"
objFirstTable.Columns.Append "CustID", adInteger
objFirstTable.Columns.Append "CustName", adVarWChar, 30
objFirstTable.Keys.Append "PK_CustID", 1, "CustID"
'Add the database table to the database catalog
objADOXDatabase.Tables.Append objFirstTable
'Clean up...
Set objFirstTable = Nothing
Set objADOXDatabase = Nothing
%>
|
