Microsoft KB Archive/140635

= ACC95: "Table '' Already Exists" Make-Table Query Error Message =

Article ID: 140635

Article Last Modified on 7/5/2002

-

APPLIES TO


 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q140635



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



SYMPTOMS
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 ' ' already exists.

NOTE: This also occurs in Microsoft Access 97 if the database is converted from Microsoft Access 2.0 and the table 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:  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.  In the query design grid, enclose the parameters within an Eval function. For example:

         Field: OrderID Table: Orders Criteria: =Eval("Forms!Form1!OrderID") </li> 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.</li></ol> </li> 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: <ol>  Create a module and type the following procedure:

<pre class="fixed_text">         Function ReturnID As Long ReturnID=Forms!Orders!OrderID End Function </li> Open the make-table parameter query in Design view.</li>  Replace the parameter in the criteria row with the ReturnID function:

<pre class="fixed_text">         Field: OrderID Table: Orders Criteria: =ReturnID </li> 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.</li></ol> </li></ul>

<div class="status_section">

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

<div class="moreinformation_section">

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:

<pre class="fixed_text">  The existing table ' ' will be deleted before you run the query. Do you want to continue anyway?

If you click Yes, the query overwrites the existing table.

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

<pre class="fixed_text">  Table ' ' already exists

Steps to Reproduce Behavior
<ol> Open the sample database Northwind.mdb.</li> Copy the Sales By Year query and name it Make Table Test.</li> Open the Make Table Test query in Design view.</li> On the Query menu, click Make Table.</li> In the Make Table box, enter the name Test Table and click OK.</li> On the Query menu, click Run.</li> In the "Forms!Sales by Year Dialog!BeginningDate" box, type 1/1/94 and click OK.</li> In the "Forms!Sales by Year Dialog!EndDate" box, type 12/31/94 and click OK.</li> When you are asked "Are you sure you want to create a new table with selected records?" click Yes.</li> Repeat steps 6-8. Note that you receive the message:

Table 'Test Table' already exists.

</li></ol>

<div class="references_section">