"operation must use an updateable query" for UPDATE query on SQL linked table in Access

0
votes

In my Access database I have one temp table - msCurrentAnnualUpload and one linked to SQL server table - msCurrent.

After I try to run Update query I get an error ("operation must use an updateable query").

Text of query is the following:

UPDATE msCurrent 

INNER JOIN msCurrentAnnualUpload ON (msCurrent.ProductID = msCurrentAnnualUpload.ProductID) AND (msCurrent.CountryCodeID = msCurrentAnnualUpload.CountryCodeID) 

SET 
msCurrent.Y1977 = msCurrentAnnualUpload.Y1977, 
msCurrent.Y1978 = msCurrentAnnualUpload.Y1978, 
msCurrent.Y1979 = msCurrentAnnualUpload.Y1979, 
msCurrent.Y1980 = msCurrentAnnualUpload.Y1980

can you help me to find an issue??

1
sqlsql-serverms-access
Hm, this is interesting, could you try this article: techonthenet.com/access/queries/update2.php - PeterRing

1 Answers

3
votes

I just copied and pasted your query into Access 2010 and it worked correctly. Verify that you can update the values in your linked table by opening it in Datasheet View and manually changing one of the [Y1977] values. If you cannot update the linked table then you'll need to confirm that

  1. Your ODBC connection actually has permission to UPDATE the table on the SQL Server.

  2. Your Access linked table has a Primary Key defined. (Open the Access linked table in Design View and check for the little gold key icon(s) as in the screenshot below.)

msCurrent.png