Published: Wednesday, January 31, 2001
Working with ADOX, Part 2
By Ramesh Balaji and
Scott Mitchell
Read Part 1
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.
| 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 & "<br>"
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 & "<br>"
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 & "<br>"
For Each objColumn in objTable.Columns
Response.Write " " & objColumn.Name & "<br>"
Next
Response.Write "<p>"
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 %>
<!--#include file="adovbs.inc"-->
<%
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 & "<br>"
For Each objColumn in objTable.Columns
Response.Write " " & objColumn.Name & "<br>"
Next
Response.Write "<p>"
End If
Next
Set objADOXDatabase = Nothing
%>
|
