When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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:

    - continued -

    1. Data Definition
    2. Data Manipulation

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

NewADOX table diagram. 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
    %>
    


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article