Cannot update a linked table field

[netsmurph]

I am trying to update a field within a linked table, but get the error message:

"Operation must use an updateable query"

I have a table (tblPOline) that is linked, and my update query is using another nested query to "filter the records that need to be updated.

The SQL is:

UPDATE qryCurrentProgress INNER JOIN tblPOLine ON qryCurrentProgress.POlineID = tblPOLine.Id SET tblPOLine.CurrentTotal = 1;

The strange thing is that if i do not include the "nested" query, this update works - so I am not sure if it is an issue to do with the linked table.

Any clues would be useful as i am stumped on this one!

Many thanks in advance.

Andrew

 

[PHV]

You may try this:
UPDATE tblPOLine
SET CurrentTotal = 1
WHERE Id In (SELECT POlineID FROM qryCurrentProgress)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netsmurph]

Sorry PHV - i think i have over simplyfied this in my post. I have one query that is used to make(qryCurrentProgress) - this returns the maximum value of each POline.

All that I wanted to do was use this query to update any matching records in the POline table.

I am not trying to update qrycurrentprogress, but it seems that as this query is not updateable, this is stopping the query from executing.

I know that i am using an query with aggregated data, but am not sure if this is the issue - i have swatted up on the following also, (but cannot work out what the issue is!)

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

Thanks if you could help me out on this one.

Andrew

 

[netsmurph]

I have used another less elegant method - creating a table based on my first query and then running the update query against this one.

I noticed you suggested in a post to someone else that this may be the best solution - so thanks for that....

Andrew