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)
Target table: CustomerSales_Retail (this is an Access local table)

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".

Query 1:

This is the most common way to do multiple table update in Access.

update CustomerSales_Retail as a
inner join CustomerSales as b on a.CustomerID = b.CustomerID
set a.SalesDate = b.SalesDate

Query 2:

This query uses an in-memory temporary table as a sub-query in the INNER JOIN statement.

update CustomerSales_Retail as a
inner join 
(
	select x.CustomerID, x.SalesDate from CustomerSales as x 
	inner join CustomerSales_Retail as y on x.CustomerID = y.CustomerID
) as b on a.CustomerID = b.CustomerID
set a.SalesDate = b.SalesDate

Query 3:

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.

update CustomerSales_Retail as a
inner join 
(
	select CustomerID, SalesDate from CustomerSales
	where CustomerID in (select distinct CustomerID from CustomerSales_Retail)
) as b on a.CustomerID = b.CustomerID
set a.SalesDate = b.SalesDate

The solution:

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

select CustomerID, SalesDate into Tbl_Temp_SalesDate 
from CustomerSales
where CustomerID in (select distinct CustomerID from CustomerSales_Retail)

Step #2: The cross-table update query.

This query uses the temporary table we created in Step 1 to update the local table.

update CustomerSales_Retail as a
inner join Tbl_Temp_SalesDate as b on a.CustomerID = b.CustomerID
set a.SalesDate = b.SalesDate

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.

Happy Updating!


Copyright© GeeksEngine.com