The "Why" for "...must use an updateable query"

[LarryP]

I occasionally run into the "...must use an updateable query..." error when
trying to update a table from a query recordset, and my standard workaround
tends to be to create a temporary table and do the updating from that. It
always bugs me, though, that I have to do that.

Have searched many forums and got a variety of information, but still need
help understanding WHY this error occurs. Typically the query involves
links, DISTINCT, and other stuff that makes it non-updateable, and I get all
that. But I'M NOT TRYING TO UPDATE THE QUERY, I JUST WANT TO USE ITS RESULTS
TO UPDATE A TABLE. Why on earth does Jet care that the query is
non-updateable if that's not what's being updated? (And, of course, if you
can tell me "just update query property x and it'll work fine," you'll really
make my day.)

PS -- no permissions issues involved.

 

[Alex Dybenko]

Hi,
general rule - all queries, joined in your update query, should be
updateable.
Also look here:
http://accessblog.net/2004/10/access-delete-update-sql-in-jet.html

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[david]

Yes, it's a weakness of the Access database engine design. On the
other hand, every attempt to improve on that design, in 2000, 2002,
2003 was broken, so I retain my original respect for the Access
designers.

The problem is in the joins: either a query can be joined in a way
that is updatable, or not.

When it analysises a query, it optimizes through the query into it's
source queries and tables and replaces the joins with it's internal
representation: it clearly doesn't have a system for keeping separate
the 'destination' join(s) of an update query, even when that would
clearly be possible.

Fixing that so that it could effectively handle two independent sets
of joins in one query would be a major update -- and see above
for my opinion of even the most minor changes they have attempted.

(david)