ACC95: "Table '<name>' already Exists" Make-Table Query Err Msg

• Microsoft Access version 7.0

SYMPTOMS

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

When you try to run a make-table parameter query more than once, you may receive the following error message and the query may not create the table:

   Table '<name>' already exists.

RESOLUTION

If a make-table parameter query creates a table with the same name as an existing table, try one of these methods to avoid the error message mentioned in the "Symptoms" section:

• Delete the existing table before running the query. If you want to automate this process, you can create a custom function to test for an existing table. If a table already exists, you can delete the table and then create a new table by running the make-table query.
• Run a delete query that removes all records from the existing table followed by an append parameter query to add records to the empty table.
• Open the make-table parameter query in Design view and modify it as follows:

      1. Remove all explicitly defined parameters from the query. To do so,
         click Parameters on the Query menu, and then delete any items
         listed in the Parameters box.
  

      2. In the query design grid, enclose the parameters within an Eval()
         function. For example:
  

            Field: OrderID
               Table: Orders
               Criteria: =Eval("Forms!Form1!OrderID")
  
      3. Run the query. Note that you receive a message that says the table
         already exists and that asks if you want to continue. You can click
         Yes to overwrite the existing table.
  
  

• Create a custom function using Visual Basic for Applications that returns the value of a parameter. Then, you can call the function from the make-table query, which passes the parameter value to the query when it is run. For example, to pass a long integer value (from a form) as a parameter, follow these steps:

      1. Create a module and type the following procedure:
  

            Function ReturnID() As Long
               ReturnID=Forms!Orders!OrderID
            End Function
  
      2. Open the make-table parameter query in Design view.
  
      3. Replace the parameter in the criteria row with the ReturnID()
         function:
  
            Field: OrderID
               Table: Orders
               Criteria: =ReturnID()
  
      4. Run the query. Note that you receive a message that says the table
         already exists and that asks if you want to continue. Click Yes to
         overwrite the existing table.
  
  

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 7.0. This problem no longer occurs in Microsoft Access 97.

MORE INFORMATION

NOTE: This error message does not occur with nonparameter make-table queries. When you run a typical make-table query more than once, you receive the following confirmation message:

   The existing table '<table name>' will be deleted before you run
   the query. Do you want to continue anyway?

Also, note that the SetWarnings Action or Method cannot be used to suppress the error message:

   Table '<name>' already exists

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.

2. Copy the Sales By Year query and name it Make Table Test.

3. Open the Make Table Test query in Design view.

4. On the Query menu, click Make Table.

5. In the Make Table box, enter the name Test Table and click OK.

6. On the Query menu, click Run.

7. In the "Forms!Sales by Year Dialog!BeginningDate" box, type 1/1/94 and click OK.

8. In the "Forms!Sales by Year Dialog!EndDate" box, type 12/31/94 and click OK.

9. When you are asked "Are you sure you want to create a new table with selected records?" click Yes.

10. Repeat steps 6-8. Note that you receive the message:

    Table 'Test Table' already exists.

REFERENCES

For more information about make-table queries, search on the phrase "make table," and then view "Create a new table from the results of a query with a make-table query" using the Answer Wizard from the Microsoft Access for Windows 95 Help menu.