Retrieving Schema Information

Both DAO and ADOX contain collections of objects that can be used to retrieve information about the database's schema. By iterating through the collections, it is easy to determine the structure of the objects in the database.

The following code demonstrates how to print the name of every table in the database by looping through the DAO TableDefs collection and the ADOX Tables collection.

DAO

Sub DAOListTables()

   Dim db As DAO.Database
   Dim tbl As DAO.TableDef

   ' Open the database
   Set db = DBEngine.OpenDatabase(".\nn.mdb")

   ' Loop through the tables in the database and print their name
   For Each tbl In db.TableDefs
      Debug.Print tbl.Name
   Next

End Sub

ADOX

Sub ADOListTables()

   Dim cat As New ADOX.Catalog
   Dim tbl As ADOX.Table

   ' Open the catalog
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\nn.mdb;"

   ' Loop through the tables in the database and print their name
   For Each tbl In cat.Tables
      If tbl.Type <> "VIEW" Then Debug.Print tbl.Name
   Next

End Sub

With DAO, the TableDef object represents a table in the database and the TableDefs collection contains a TableDef object for each table in the database. This is similar to ADO, in which the Table object represents a table and the Tables collection contains all the tables.

However, unlike DAO, the ADO Tables collection may contain Table objects that aren't actual tables in your Microsoft Jet database. For example, row-returning, non-parameterized Microsoft Jet queries (considered Views in ADO) are also included in the Tables collection. To determine whether or not the Table object represents a table in the database, use the Type property. The following table lists the possible values for the Type property when using ADO with the Microsoft Jet Provider.

Type	Description
ACCESS TABLE	The Table is a Microsoft 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 table.
VIEW	The Table is a row-returning, non-parameterized query.

In addition to being able to retrieve schema information using collections in ADOX, you can use the ADO OpenSchema method to return a Recordset containing information about the tables in the database. See Appendix C: Microsoft Jet 4.0 OLE DB Provider Defined Property Values for the schema rowsets GUIDs that are available in ADO when using the Microsoft Jet Provider.

In general, it is faster to use the OpenSchema method rather than looping through the collection, because ADOX must incur the overhead of creating objects for each element in the collection. The following code demonstrates how to use the OpenSchema method to print the same information as the previous DAO and ADOX examples.

Sub ADOListTables2()

   Dim cnn As New ADODB.Connection
   Dim rst As ADODB.Recordset

   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\nn.mdb;"

   ' Open the tables schema rowset
   Set rst = cnn.OpenSchema(adSchemaTables)

   ' Loop through the results and print
   ' the names in the debug window
   Do Until rst.EOF
      If rst.Fields("TABLE_TYPE") <> "VIEW" Then
         Debug.Print rst.Fields("TABLE_NAME")
      End If
      rst.MoveNext
   Loop

End Sub