Pass-Through query used to update an access table

[DavidES]

The following update query works fine:

UPDATE WebPrice SET WebPrice.InvClass = "COUNT"
WHERE (((WebPrice.InvClass)<>"PART"));

When I add a pass-through query to select criteria, the query is no longer
updateable as in:

UPDATE WebPrice INNER JOIN InvCountPTsql ON WebPrice.Number =
InvCountPTsql.NUMBER SET WebPrice.InvClass = IIf("DISCONT"=Yes,"DISC","COUNT")
WHERE (((WebPrice.InvClass)<>"PART"));

I know that pass through queries are not updateable but I need to use the
information to update my local access table. Can someone either tell me what
I am doing wrong or tell me what I need to do to accomplish my task?

 

[John Spencer]

Try using a sub-query: something like the following - probably slow.

UPDATE WebPrice
SET WebPrice.InvClass = IIf("DISCONT"=True,"DISC","COUNT")
WHERE WebPrice.InvClass<>"PART"
AND WebPrice.Number IN
(SELECT InvCountPTsql.NUMBER FROM InvCountPTsql)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[DavidES]

Thanks John!

I never thought of a sub-query and it processed 4,000 records in 60 seconds
or less.
Thanks for the help.

DavidES