1

My system is designed by Access 2013. I setup a query to get data from SQL Server 2014 by ODBC connection. The connection does not have problem. I can open query1 to see all records. Query1 is complicated and join couple tables together. I want to use the query records to update local table. The error message is:

Run-time error '3073'

Operation must use updateable query.

str = "UPDATE table1 INNER JOIN query1 AS A ON Table1.Item = A.Item SET Table1.Qty = A.Qty"

DoCmd.RunSQL str
YellowLarry
  • 383
  • 1
  • 4
  • 16
  • UPDATE is OK, if I join two tables. Error message comes out when I use query on the join. – YellowLarry Dec 24 '16 at 06:40
  • Is update statement in the str working fine in Sql server management studio directly? – Shakeer Mirza Dec 24 '16 at 06:45
  • Update will update local table, not SQL Server table. I cannot test it on SQL Server Management. – YellowLarry Dec 24 '16 at 06:48
  • 1
    Unfortunately, you can't. *Access always treats an UPDATE which includes a joined pass-through query as read-only. And that holds true even when the UPDATE does not attempt to alter values in the pass-through.* Insert query1 into a temp table and use this for the join in the update query. http://stackoverflow.com/questions/28082680/query-not-updateable and http://stackoverflow.com/questions/19789709/operation-must-use-an-updateable-query-error-in-ms-access – Andre Dec 24 '16 at 08:43

0 Answers0