Working with ADOX, Part 2

By Ramesh Balaji and Scott Mitchell

In Part 1 we looked at what, exactly, ADOX is and how to use it to create an Access database and database table. In this part we'll look at using the ADOX object model to programmatically obtain a listing of database tables and their columns.

- continued -

Listing the Tables and Columns of a Database

Using the ADOX library is only one way (of many) to list the tables and columns of a database. Another object-based way that works with both Access and MS-SQL Server is using the Schema object. For more information on this, be sure to read: Listing the Tables and Columns in a Database. A strictly SQL-based approach can also be implemented (but will only work on MS-SQL Server. For more information on this method, be sure to check out: Using the sysobjects Table.

Listing the Tables and Columns in a Database
Listing the tables and columns in a database using the ADOX library is fairly straightforward. Start by creating a Catalog object. You then should specify the database whose tables/columns you are interested in using the ActiveConnection property like so:

Dim objADOXDatabase Set objADOXDatabase = Server.CreateObject("ADOX.Catalog") objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\customer.mdb"

Since the Catalog object contains a collection of Table objects as its Tables property we can use a For Each ... Next loop to step through the database tables and display them:

Dim objTable For Each objTable in objADOXDatabase.Tables Response.Write objTable.Name & " " Next

Pretty simple, eh? This will, unfortunately, list all of the tables in the database, both system and user-created tables. With our customer.mdb Access database, this will produce the following output (using Access 2000):

MSysAccessObjects
MSysACEs
MSysObjects
MSysQueries
MSysRelationships
NewADOX

If we only want to list the user-created tables, we can first check the Type property of the Table object and make sure its equal to TABLE. The following slightly modified script will display only the NewADOX table name:

Dim objTable For Each objTable in objADOXDatabase.Tables 'Only display user-created tables... If objTable.Type = "TABLE" then Response.Write objTable.Name & " " End If Next

To display the tables for all of our databases, we can simply use a For Each ... Next loop to loop through the Columns collection of the Table object. For example, with the following code:

Dim objTable, objColumn For Each objTable in objADOXDatabase.Tables If objTable.Type = "TABLE" then Response.Write objTable.Name & " " For Each objColumn in objTable.Columns Response.Write "   " & objColumn.Name & " " Next Response.Write "" End If Next

Will produce the following output:

NewADOX
   CustID
   CustName

The Column object contains a number of properties other than the Name that you can display here. For more information on the Column object, check out these technical docs.

Well, that wraps up our examination of the ADOX libraries. ADOX provides a useful, object-based approach for creating, modifying, and reading database information. For more information on ADOX, be sure to check out these article:

Happy Programming!

By Ramesh Balaji and Scott Mitchell

Complete Source Code of Table/Column Listing Example

<% Option Explicit %>  <% Dim objADOXDatabase Set objADOXDatabase = Server.CreateObject("ADOX.Catalog") objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\customer.mdb" Dim objTable, objColumn For Each objTable in objADOXDatabase.Tables If objTable.Type = "TABLE" then Response.Write objTable.Name & " " For Each objColumn in objTable.Columns Response.Write "   " & objColumn.Name & " " Next Response.Write "" End If Next Set objADOXDatabase = Nothing %>