Listing the Tables in an Access Database

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

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.

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.