Listing Database Tables

The procedure in Hands-On 11-10 generates a list of tables in the Northwind database. It uses the ADOX Catalog object to gain access to the database, then iterates through the Tables collection to retrieve the names of Access tables, system tables, and views. The ADOX Tables collection stores various types of Table objects, as shown in Table 11-2.

Table 11-2: Types of tables in the ADOX Tables collection




An Access system table


A linked table from a non-ODBC data source


A linked table from an ODBC data source


A Microsoft Jet system table


A Microsoft Access table


A table from a row-returning, non-parameterized query

© Hands-On 11-10: Creating a List of Database Tables

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the ListTbls procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub ListTbls()

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

Set cat = New ADOX.Catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

For Each tbl In cat.Tables If tbl.Type <> "VIEW" And _

tbl.Type <> "SYSTEM TABLE" And _ tbl.Type <> "ACCESS TABLE" Then Debug.Print tbl.Name Next tbl

Set cat = Nothing

MsgBox "View the list of tables in the Immediate window." End Sub

To list tables in your database you can also use the OpenSchema method of the ADO Connection object (see the section called "Listing Tables and Fields" later in this chapter).

Part II

0 0

Post a comment