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, 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.

    - 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 & "<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 "&nbsp;&nbsp;&nbsp;" & 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 "&nbsp;&nbsp;&nbsp;" & objColumn.Name & "<br>"
        Next
    
        Response.Write "<p>"
      End If
    Next
    
    Set objADOXDatabase = Nothing
    %>
    


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