SQL Update Woes in Ms Access - Operation Must Use an Updateable Query

Operation must use an updateable query error in MS Access

Whether this answer is universally true or not, I don't know, but I solved this by altering my query slightly.

Rather than joining a select query to a table and processing it, I changed the select query to create a temporary table. I then used that temporary table to the real table and it all worked perfectly.

SQL Update woes in MS Access - Operation must use an updateable query

A subquery of (SELECT ID FROM t2) can't work unless there is only one record in t2. Which ID are you expecting to be used?

The error message that is being reported normally occurs when you have joins and are not including all of the primary keys necessary to update back to tables in a data bound form (for example, your original DISTINCT destroys information about keys, so if it was bound to a form, the form would not be able to save back).

The fact you are using DISTINCT there would make me suspicious that the sub query is returning more than one row in your more complex example. This is probably the most common problem with assigning out of a sub query result: under-constraining the where clause.

Another problem I have seen with assigning out of a subquery is if the syntax of the inner query is incorrect. At least with SQL 2000 and 2005 back ends, the query processor will silently fail and return NULL in such cases. (This is, as far as I can tell, a bug: I see no reason why something that will return an error at the top level would be silently permitted in a subquery... but there it is.)

EDIT:
Just to ensure that neither Paul or I wasn't going crazy, I created the following tables:

t1 | ID, FK, Data
t2 | ID2, Data2

I did not put any constraints except a primary key on ID and ID2. All fields were text, which is different from what I normally use for IDs, but should be irrelevant.

t1:

ID  FK  Data
Key1        Data1
Key2        Data2
Key3        Data3

t2:

ID2 Data2
Key1    DataA
Key2    DataB
Key3    DataC

A query of the form:

UPDATE t1 SET t1.FK = (select ID2 from t2 where t2.ID2 = t1.ID);

Failed with the same message Paul got.

select *, (select ID2 from t2 where t2.ID2 = t1.ID) as foreign from t1, 

works as expected, so we know the subquery syntax is not to blame.

UPDATE t1 SET t1.FK = 'Key1'

also works as expected, so we don't have a corrupt or non updateable destination.

Note: if I change the database backend from native to SQL 2005, the update works! A bit of googling around, and I find Access MVPs suggesting DLOOKUP to replace a subquery:

http://www.eggheadcafe.com/software/aspnet/31849054/update-with-subquerycomp.aspx

Apparently this is a bug in Access SQL, one that is avoided when using a SQL Express 2000 or higher back end. (The google results for "access update subquery" support this theory).

See here for how to use this workaround: http://www.techonthenet.com/access/functions/domain/dlookup.php

operation must use an updateable query - access

I think the reason Access treats your query as non-updateable is due to the subquery GROUP BY. You should be able to create an updateable query by using DSum.

UPDATE TABLE1 AS a
SET a.TRIAL = a.TRIAL
    + DSum("AMOUNT", "TABLE2", "ACCODE=" & a.ACCODE)

If ACCODE is text instead of numeric data type, add quotes around the value in the DSum expression.

UPDATE TABLE1 AS a
SET a.TRIAL = a.TRIAL
    + DSum("AMOUNT", "TABLE2", "ACCODE='" & a.ACCODE & "'")

Operation must use an updatable query

Unfortunately this is an inherent restriction of the JET database engine used by MS Access: no part of an update query may use aggregation, else the resulting recordset is not updateable.

---

There are a couple of workarounds:

You can use a domain aggregate function, such as DCount:

update tracking set batchcount = dcount("*", "Batch")
where reportingdate = date();

Alternatively, you can use a temporary table to store the result of the count, and then update the records using the value held in the table, e.g.

select count(*) as cnt into temptable from batch

update tracking, temptable set tracking.batchcount = temptable.cnt
where tracking.reportingdate = date();    

How to fix: Operation must use an updateable query on update statement in Access VBA

JOIN tables instead of nesting.

SQL = "UPDATE Master_Table INNER JOIN Change_Table ON Master_table.B_ID = Change_Table.B_ID SET Master_Table.Email = Change_Table.Email"

Use Access query builder to get correct syntax then copy/paste from SQLView into VBA. Will often need to do some minor edits in VBA but it should get what you want.

---

Related Topics