Microsoft KB Archive/225895

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

Article ID: 225895

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q225895



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

This article applies to a Microsoft Access database (.mdb) and to 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</li></ul> </li>  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 </li>  Type the following line in the Immediate window, and then press ENTER: FindTables </li></ol>

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

Additional query words: prb

Keywords: kbprb kbpending KB225895

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.