Operation must use an updatable query. (Error 3073) Microsoft Access

Question

I have written this query:

UPDATE tbl_stock1 SET 
tbl_stock1.weight1 = (
    select (b.weight1 - c.weight_in_gram) as temp 
    from
        tbl_stock1 as b,
        tbl_sales_item as c 
    where
        b.item_submodel_id = c.item_submodel_id 
        and b.item_submodel_id = tbl_stock1.item_submodel_id 
        and b.status <> 'D' 
        and c.status <> 'D'
    ), 
tbl_stock1.qty1 = (
    select (b.qty1 - c.qty) as temp1 
    from
        tbl_stock1 as b,
        tbl_sales_item as c 
    where 
        b.item_submodel_id = c.item_submodel_id 
        and b.item_submodel_id = tbl_stock1.item_submodel_id 
        and b.status <> 'D' 
        and c.status <> 'D'
    )
WHERE
    tbl_stock1.item_submodel_id = 'ISUBM/1' 
    and tbl_stock1.status <> 'D';

I got this error message:

Operation must use an updatable query. (Error 3073) Microsoft Access

But if I run the same query in SQL Server it will be executed.

Thanks, dinesh

Answer 1

When the query is correct, the usual reason for this error message is that the user account used to run the query doesn't have write permission to the database file.

Check that the database file doesn't have the read only attribute set.

If you are running the query from an ASP.NET page, it's normally either the user account ASPNET or IIS_WPG that is used to run the code. You have to edit the file permissions to allow write permission to the relevant user account.

Answer 2

I'm quite sure the JET DB Engine treats any query with a subquery as non-updateable. This is most likely the reason for the error and, thus, you'll need to rework the logic and avoid the subqueries.

As a test, you might also try to remove the calculation (the subtraction) being performed in each of the two subqueries. This calculation may not be playing nicely with the update as well.

Answer 3

In the query properties, try changing the Recordset Type to Dynaset (Inconsistent Updates)

Answer 4

You are updating weight1 and qty1 with values that are in turn derived from weight1 and qty1 (respectively). That's why MS-Access is choking on the update. It's probably also doing some optimisation in the background.

The way I would get around this is to dump the calculations into a temporary table, and then update the first table from the temporary table.

Answer 5

Consider this very simple UPDATE statement using Northwind:

UPDATE Categories
   SET Description = (
                      SELECT DISTINCT 'Anything' 
                        FROM Employees
                     );

It fails with the error 'Operation must use an updateable query'.

The Access database engine simple does not support the SQL-92 syntax using a scalar subquery in the SET clause.

The Access database engine has its own proprietary UPDATE..JOIN..SET syntax but is unsafe because, unlike a scalar subquery, it doesn’t require values to be unambiguous. If values are ambiguous then the engine silent 'picks' one arbitrarily and it is hard (if not impossible) to predict which one will be applied even if you were aware of the problem.

For example, consider the existing Categories table in Northwind and the following daft (non-)table as a target for an update (daft but simple to demonstrate the problem clearly):

CREATE TABLE BadCategories
(
 CategoryID INTEGER NOT NULL, 
 CategoryName NVARCHAR(15) NOT NULL
)
;
INSERT INTO BadCategories (CategoryID, CategoryName) 
   VALUES (1, 'This one...?')
;
INSERT INTO BadCategories (CategoryID, CategoryName) 
   VALUES (1, '...or this one?')
;

Now for the UPDATE:

UPDATE Categories 
       INNER JOIN (
                   SELECT T1.CategoryID, T1.CategoryName
                     FROM Categories AS T1
                   UNION ALL 
                   SELECT 9 - T2.CategoryID, T2.CategoryName
                     FROM Categories AS T2
                  ) AS DT1
       ON DT1.CategoryID = Categories.CategoryID
   SET Categories.CategoryName = DT1.CategoryName;

When I run this I'm told that two rows have been updated, funny because there's only one matching row in the Categories table. The result is that the Categories table with CategoryID now has the '...or this one?' value. I suspect it has been a race to see which value gets written to the table last.

The SQL-92 scalar subquery is verbose when there are multiple clauses in the SET and/or the WHERE clause matches the SET's clauses but at least it eliminates ambiguity (plus a decent optimizer should be able to detects that the subqueries are close matches).

The Access database engine's lack of support for the SQL-92 scalar subquery syntax is for me its worst 'design feature' (read 'bug').

Also note the Access database engine's proprietary UPDATE..JOIN..SET syntax cannot anyhow be used with set functions ('totals queries' in Access-speak). See Update Query Based on Totals Query Fails.