Custom Search
 


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)
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



Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to use ADOX to create unique composite index - the VBA approach
7.Three efficient ways to get the number of records by using VBA
8.How to create a composite unique index (not as a primary key) in MS Access
9.Use VBA to get the correct number of records in a Recordset object
10.Disable Access Prompt when a record is changed, table deleted, or action queries run
11.How to hide and unhide a MS Access object
12.How to return multiple values from a VBA function (Part 3)
13.How to return multiple values from a VBA function (Part 2)
14.How to return multiple values from a VBA function (Part 1)
15.Three ways to programmatically duplicate a table in MS Access by VBA
16.Create a DLL by CSharp or VB.Net for VBA
17.How to correctly reference and call a DLL
18.How to register a C# or VB.Net DLL
19.Email address validation by Regular Expressions using VBA
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

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.

 
Home | Feedback | Terms of Use | Privacy Policy