The information in this article applies to:
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
- Create a new database called TstUpdt.mdb in Microsoft Access 7.0 or
earlier.
- 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.
- 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
- Save the qryOrderInfo query and close it.
- 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
- 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.
- Save the qryUpdateOrder query and close the database.
- Start Microsoft Access 97 and open TstUpdt.mdb. Convert the database
when prompted.
- 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.
- Open the qryUpdateOrder query in Design view.
- On the View menu, click SQL View.
- Delete the word DISTINCTROW from the query's SQL statement.
- 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.