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