UPDATE SET SELECT error "Operation must use an updateable query"

Question

I have an Access database with an item table "VendorItem" with (ID, Vendor, Price) Fields and another "ItemPrice" with (ItemName, AveragePrice) Fields.

I want to run an SQL query that will update the average price of the item from a specific vendor, let's say 'xxx'.

The error I get is "Operation must use an updateable query". This code is simple but I haven't been able to find the cause of this error. How could I change the string to get what I want?

I have:

Sub MP()

Dim strSQL As String

strSQL = "UPDATE ItemPrice SET AveragePrice=(SELECT AVG(Price) FROM VendorItem WHERE Vendor='xxx') WHERE ItemName='xxx'"
DoCmd.RunSQL strSQL

End Sub

It looks like ms access doesn't like aggregate functions in update. See this question for more details: http://stackoverflow.com/q/871905/1385896 — Filipe Silva, Nov 05 '13 at 00:24

score 1 · Accepted Answer · answered Nov 05 '13 at 00:25

Access domain functions can be used to avoid the "must use updateable query" complaint. In this case, use the DAvg Function.

First test this as a new query in the query designer. Adjust as needed, then adapt your code to use the working SQL.

UPDATE ItemPrice
SET AveragePrice = DAvg('Price', 'VendorItem', 'Vendor="xxx"')
WHERE ItemName='xxx'

UPDATE SET SELECT error "Operation must use an updateable query"

1 Answers1