PRB: ListTables Method Returns Odd Names

Article ID: Q106188
Creation Date: 02-NOV-1993
Revision Date: 19-SEP-1996

The information in this article applies to:

Microsoft Access versions 1.0 and 1.1

SYMPTOMS

When you use the ListTables method to list table or QueryDef names, you may see odd names that are not objects in the Database window. For example, the ListTables method may return a name similar to

   Customer Mailing Labels0000

which is neither a table nor a QueryDef in your database. There may be an object with a similar name in the Database window, but no actual table or QueryDef with the same name.

CAUSE

The odd name is the name of a hidden query that was created by running a report. Although the QueryDef cannot be seen in the Database window, it does exist and will be found by the ListTables method.

RESOLUTION

Hidden QueryDefs are retained in the database until the report is deleted and you compact the database.

MORE INFORMATION

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual.

Steps to Reproduce Behavior

Create a new report and save it as Test1.
Run the Test1 report at least once.
Open a module and enter the following function:

'=============================================================

      ' The following function ListMyTables() can be run from the
      ' Immediate window by typing "?ListMyTables()" (without the
      ' quotation marks). It returns the Table/QueryDef name
      ' and TableType.

'=============================================================

         Function ListMyTables ()
            Dim MyDB As Database, MySnap As Snapshot
            Set MyDB = CurrentDB()
            Set MySnap = MyDB.ListTables()
            MySnap.MoveFirst
               Do Until MySnap.EOF
               Debug.Print MySnap.name & " " & MySnap.TableType
               MySnap.MoveNext
            Loop
         End Function

Run the function in the Immediate window by typing:

      ? ListMyTables()

Note that "test100005" is returned in the Immediate window. This is the name of the hidden QueryDef created by the Test1 report.

If you delete the report, compact the database, and then run the function again, "test100005" will not be returned.

REFERENCES

Microsoft Access "Language Reference," version 1.0, pages 292-294

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.