PRB: New Query with Same Name As Existing Table Deletes Table

Article ID: Q132069
Creation Date: 27-JUN-1995
Revision Date: 01-DEC-1996

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you create a query and save it with the same name as an existing table, Microsoft Access prompts you with one of the following messages:

   You have given this query the same name as an existing table in your
   database.

   -or-

   Replace existing Table '<table name>'?

where <table name> is the name of your table.

   -or-

   Existing Query '<query name>' will be deleted before running the
   query. Continue anyway?

   -or-

   Existing Table '<table name>' will be deleted before running the
   query. Continue anyway?

With any of these messages, if you click the Yes button, Microsoft Access deletes the table and all the data in it or deletes the query. Once they are deleted, you cannot recover the information.

CAUSE

Microsoft Access stores tables and queries in the same "namespace." It does so because tables and queries can both be used as sources for new objects, such as new forms or new queries. Because they are stored in the same "namespace," creating a query with the same name as an existing table creates a naming conflict. Microsoft Access handles this naming conflict by deleting the existing object after asking you for confirmation, and then replacing it with the new one.

NOTE: In the case of a make-table query, in Microsoft Access versions 1.x or 2.0, if you choose Yes when asked if Microsoft Access should continue anyway, after the message informing you that the table will be deleted, you receive the message:

   Couldn't find input table or query '<query or table name>'

At this point, the table no longer exists and the query fails. You cannot retrieve the table, not even by clicking the Undo command on the Edit menu.

RESOLUTION

Do not use the same name for a query and a table.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

Copy the Employees table and save it with the name Emps. Check that the new Emps table is listed in the Tables list in the Database window.

Create a new query and try to save it as Emps (the same name as the new table).
In Microsoft Access 7.0 and 97: -------------------------------
You receive the following error message:

You have given this query the same name as an existing table in your database.

When you click OK, you return to your query design and cannot save the query with the table name.
In Microsoft Access 1.x and 2.0: --------------------------------
Click the Yes button when prompted with the following message:

Replace existing Table 'Emps'?

Click the Table tab in the Database window. Note that Microsoft Access deleted the Emps table.

REFERENCES

For more information about creating queries, search the Help Index for "queries, creating."

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.