How to do cross-table update queries in MS Access - the right way
This article shows a great tip about how to avoid the common error when MS Access executes cross-table update queries.
MS Access error: "Operation must use an updatable query. (Error 3073)"
I came across this error when I was trying to update data in a local Access table by using the matching data in a linked table in Oracle.
If the two tables are both local Access tables, my update query is fine to run and can correctly update the data in the target table, but because one of the tables (the source table in my case) was a linked table, I kept getting this error.
Here are the 3 update queries I tried and got the same error each time. CustomerSales is a linked table (the source table - data came from this table). CustomerSales_Retail is a local table (target table - contains the data I wanted to update).
Source table: CustomerSales (this is a linked table in Access)
What we want to do is to change SalesDate value in the CustomerSales_Retail table to new sales date stored in the linked table. The three queries below produce the same result, but none of them succeeded due to the Access error "Operation must use an updatable query".
This is the most common way to do multiple table update in Access.
This query uses an in-memory temporary table as a sub-query in the INNER JOIN statement.
Here is another way of doing the same thing as Query 2, but here in the sub-query it uses IN rather than INNER JOIN. It's less efficient than using inner join because join can take advantage of the index on the join column if it exists.
To fix the problem, I had to split my original update query into two queries. The first one is a make-table query and the other one is a cross-table update query.
Step #1: Make-Table query - create an intermediate (temporary but physical) local table
Step #2: The cross-table update query.
This query uses the temporary table we created in Step 1 to update the local table.
To sum up, when we do multiple table updates in MS Access, first we need to make sure that the source table contains unique data in the joined column(s). This may be a primary key column or a column with unique index, or columns with combined uniqueness (also known as composite unique index). Uniqueness prohibits duplicated values in the column(s).
Secondly, if the source table is a linked table, we can resort to a two-step process. Building a temporary table in Access that contains the data obtained from the linked table, then do the update locally by using the temporary table as the source table because the temporary table contains the qualified data from the linked table. We then update the matching data in the local target table by using a join with the temporary table.
Copyright © 2013 GeeksEngine.com. All Rights Reserved.
This website is hosted by LunarPages.
No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.