ACC2000: ActiveX Data Objects (ADO) Includes Queries and Views in the Tables Collection

ID: Q225895

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SYMPTOMS

When you use ActiveX Data Objects (ADO) to programmatically traverse the Tables collection, you see the names of queries and views included as part of the collection.

RESOLUTION

Test for the type of object within the Tables collection. The table types can be:

ACCESS TABLE

SYSTEM TABLE

SYSTEM VIEW

TABLE

VIEW

The following ADO code will only display user created tables, or tables created if you encounter import errors:


Sub FindOnlyTables()

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

   cat.ActiveConnection = CurrentProject.Connection

   For Each tbl In cat.Tables
      If tbl.Type = "TABLE" Then
         If tbl.Name <> "dtproperties" Then
            Debug.Print tbl.Name
         End If
      End If
   Next tbl

End Sub

STATUS

Microsoft Access 2000 uses ANSI standard SQL-92, which includes both queries and views in the Tables collection.

MORE INFORMATION

When you use the ADO object model to iterate through the Tables collection of an Access database, the names of queries will be included as part of the collection.

When you use the ADO object model to iterate through the Tables collection of an Access project, the names of views will be included as part of the collection.

Steps To Reproduce Behavior

Open the sample database Northwind.mdb or the sample Access project NorthwindCS.adp.

Insert a new module.

On the Tools menu, click References, and select the following references if they are not already selected (checked):

Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.1 for DDL and Security

Type the following ADO code:


Sub FindTables()

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

   cat.ActiveConnection = CurrentProject.Connection

   For Each tbl In cat.Tables
      Debug.Print tbl.Name
   Next tbl

End Sub

Type the following line in the Immediate window, and then press ENTER:


FindTables

Note that the Immediate window lists tables, along with queries or views.

Additional query words: prb

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb