Microsoft Office 2000/Visual Basic Programmer's Guide

Listing the Tables in an Access Database

In ADOX, the Table object represents a table and the Tables collection provides access to information about all tables in the database. You can use the Tables collection to list all tables within a database. However, the Tables collection may also contain Table objects that aren't actual tables in your Access database. For example, a query that returns records but doesn't have parameters (what is known as a select query in Access) is considered a View object in ADOX, and is also included in the Tables collection. The ADOX Tables collection also includes linked tables and system tables. You can distinguish between different kinds of Table objects by using the Type property. The following table lists the possible string values returned for the Type property when you use ADO with the Microsoft Jet 4.0 OLE DB Provider.

Type	Description
ACCESS TABLE	The table is an Access system table.
LINK	The table is a linked table from a non-ODBC data source.
PASS-THROUGH	The table is a linked table from an ODBC data source.
SYSTEM TABLE	The table is a Microsoft Jet system table.
TABLE	The table is a standard table.
VIEW	The table is a query that has no parameters and returns records.

The following procedure shows how to print the names of all tables in the database.

Sub ListAccessTables(strDBPath As String)
   Dim catDB As ADOX.Catalog
   Dim tblList As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open the catalog.
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                         "Data Source=" & strDBPath

   ' Loop through all the tables, but not queries,
   ' and print their names and types.
   For Each tblList In catDB.Tables
      If tblList.Type <> "VIEW" Then 
         Debug.Print tblList.Name & vbTab & tblList.Type
      End If
   Next
   
   Set catDB = Nothing
End Sub

Alternatively, you can use the ADO OpenSchema method to return a Recordset object that contains information about the tables in the database. When you use this method, you can restrict the list of tables returned on the basis of Type as well as Name properties. In general, it is faster to use the OpenSchema method rather than loop through the ADOX Tables collection, because ADOX must incur the overhead of creating objects for each element in the collection. The following procedure shows how to use the OpenSchema method to print the same information as the previous ADOX example.

Sub ListAccessTables2(strDBPath)
   Dim cnnDB As ADODB.Connection
   Dim rstList As ADODB.Recordset

   Set cnnDB = New ADODB.Connection

   ' Open the connection.
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   ' Open the tables schema rowset.
   Set rstList = cnnDB.OpenSchema(adSchemaTables)

   ' Loop through the results and print the
   ' names and types in the Immediate pane.
   With rstList
      Do While Not .EOF
         If .Fields("TABLE_TYPE") <> "VIEW" Then
            Debug.Print .Fields("TABLE_NAME") & vbTab & _
               .Fields("TABLE_TYPE")
         End If
         .MoveNext
      Loop
   End With
   cnnDB.Close
   Set cnnDB = Nothing
End Sub

The ListAccessTables and ListAccessTables2 procedures can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.