Update Query

[goslincm]

Good morning, I'll try to describe this in 3 parts:

Part 1: I have 2 linked tables that are my source for a query that I am creating for the purpose of being able to create a new field name (by parsing off some characters). Works fine, in my new saved query I have the field name I need.

Part 2: Now I am joining that query to a table in my local database by the new field name from the query, to the same field name in the local table.

Part 3: I am also bringing in a linked table that is my destination, which contains a field I need to update from my previous joins above. When I run this as a select query, I get the correct set of records. I have even had it run successfully before.

However, yesterday when trying to run this update query I am getting the error 3073 Operation must use an updateable query.

Can someone please help me get past this? I really don't understand why it would have worked a few times, but not work now.

[Stewart Ross]

Hi. One of Access's weak points is that it has a very limited range when it comes to what it considers 'updateable'. If you could please post the SQL for your update query we would be able to tell whether it is likely to fall into the non-updateable camp - which it will if it includes any form of grouping (often used for calculated fields).

If it turns out that the query is not updateable then one common workaround is to change the selection query to a make-table query to generate a temporary table containing what you wish to join to the main table for the update, then to do the update from a separate query joining the temporary table to the main table. Table-to-table joins are generally updateable, whereas query-to-table joins are often not - and very frustrating it is too.

If the two-stage solution is adopted it can be managed by putting the two actions together into a single macro so that the two actions - generating the temporary table then updating the main table - always run together.

-Stewart