ACC97: Error Running Update Query in Converted Database

Last reviewed: May 21, 1997
Article ID: Q163054
The information in this article applies to:
  • Microsoft Access 97

SYMPTOMS

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

When you run an update query in a database which has been converted to Microsoft Access 97, you may receive the following error message:

   Operation must use an updatable query.

This occurs even if the update query ran without the error in Microsoft Access 7.0 or earlier.

CAUSE

The update query is based on a select query. In Microsoft Access 7.0 or earlier, a select query returns only unique records by default; the SQL statement that defines the query contains the predicate DISTINCTROW. However, a select query in Microsoft Access 97 does not contain DISTINCTROW by default, and all records are returned, even if an entire record is a duplicate of another record in the query results.

RESOLUTION

Open the update query in SQL view and remove the word "DISTINCTROW" from the SQL statement. Then run the update query again.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a new database called TstUpdt.mdb in Microsoft Access 7.0 or earlier.

  2. Import the Customers table, the Orders table, and the Order Details table from the sample database Northwind.mdb (or NWIND.MDB in version 2.0) into the new database.

  3. Create a new query in Design view based on the Customers, Orders, and Order Details tables.

    NOTE: In the following query, [CustomerID], [OrderID], [ContactName], and [EmployeeID] each contain a space in version 2.0.

           Query: qryOrderInfo
           ----------------------------------------------------
           Type: Select Query
           Join: Customers.[CustomerID] <-> Orders.[CustomerID]
           Join: Orders.[OrderID] <-> [Order Details].[OrderID]
    

           Field: ContactName
    
              Table: Customers
           Field: EmployeeID
              Table: Orders
              Criteria: 2
           Field: Quantity
              Table: Order Details
    
    

  4. Save the qryOrderInfo query and close it.

  5. Create a new update query in Design view based on the qryOrderInfo query.

    NOTE: In the following query, [ContactName] contains a space in version 2.0.

           Query: qryUpdateOrder
           ----------------------
           Type: Update Query
    

           Field: ContactName
    
              Table: qryOrderInfo
              Update To: "XXX"
           Field: Quantity
              Table: qryOrderInfo
              Update To: 33
    
    

  6. Run the query. You will see a message indicating the number of rows that will be updated; click No (or Cancel in version 2.0) to cancel the update.

  7. Save the qryUpdateOrder query and close the database.

  8. Start Microsoft Access 97 and open TstUpdt.mdb. Convert the database when prompted.

  9. Run the qryUpdateOrder query. When you receive the alert that you are about to run an Update query that will modify data in your table, click Yes. Note that you receive the error message:

           Operation must use an updatable query.
    

        To resolve the error, continue with the following steps.
    

  10. Open the qryUpdateOrder query in Design view.

  11. On the View menu, click SQL View.

  12. Delete the word DISTINCTROW from the query's SQL statement.

  13. On the Query menu, click Run. You will see a message indicating the

        number of rows that will be updated; click Yes. Note that the query
        runs without errors.
    

REFERENCES

For more information about the DISTINCTROW predicate, search the Help Index for "UniqueRecords property," or ask the Microsoft Access 97 Office Assistant.


Additional query words: conversion action 3073 select delete Distinct row
append
Keywords : CnvProb kbusage QryUpdat
Version : 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb


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.

Last reviewed: May 21, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.