Apologies if this is poor etiquette; I just crossposted this to the MS Access forum.

I want to update all rows in one table with values from another. The two tables have a one-to-many relationship (D[one]-----P[many]).

As near as I can figure, the SQL needs to look like:

Unfortunately, I'm trying to do this in an MS Jet DB (through Access 2000 & ADO 2.5), so I'm not sure if my SQL is wrong-headed, or if the application simply won't do what I'm trying to tell it.

As the above stands, I get an error: "Operation must use an updateable query." The help topic associated with the error suggests that the query may be attempting to update the one side of a one-to-many relationship (it is).

However, if I substitute a static value for the SET subquery (say, 0), it works fine, but obviously not as intended. In other words, this works fine:

Does anyone have any pointers or suggestions as to what I'm doing wrong?

(Wild speculation, but could this have something to do with the type of cursor in play?)

Is that valid syntax in Access? It certainly isn't in Oracle.

Also, I don't understand the following query in the original question:

UPDATE D
SET D.[Total] = 0
WHERE D.[Acct]
IN (SELECT [Acct] FROM D)

What is the purpose of the WHERE clause? It is equivalent to saying:

UPDATE D
SET D.[Total] = 0
WHERE D.[Acct] IS NOT NULL;