operation must use an updateable query - access

Question

I want to update a table by using the join in access - 2007

UPDATE TABLE1 A INNER JOIN (SELECT ACCODE, SUM(AMOUNT) AS SUM_AMOUNT 
FROM TABLE2 GROUP BY ACCODE) B ON A.ACCODE = B.ACCODE
SET A.TRIAL = A.TRIAL + SUM_AMOUNT

it gives me error that

operation must use an updateable query

i have try with below query and no error is here

UPDATE TABLE1 A INNER JOIN TABLE2 B ON A.ACCODE = B.ACCODE
SET A.TRIAL = A.TRIAL + SUM_AMOUNT

please help me to find what is wrong with first query

Answer 1

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 & "'")

Answer 2

I just had this issue and it was due to permissions on the table. I made a copy of the linked table to test my update query on, and kept getting this error message. I was pretty sure my query would be ok (it was a simple update) so I ran it on the actual table and I didn't get the error. Just wanted to add this for anyone else that comes across this in the future!