[AccessD] Operation must use an updatable query

William Benson (VBACreations.Com) vbacreations at gmail.com
Wed Jun 8 13:50:39 CDT 2011


I should wait for someone  else to tell me whether this is resolveable but let me point out that the Google research I have done says it is a common problem with Access SQL... JOINS and Update queries do not get along. There was some person saying that if there was a unique key being joined ON then there was some way it could be made updatable. I am not able to achieve this unique key status in the two joined sources, so I have given up.

Below is the replacement query, without any grouping. What is interesting (and strange) is that although there are only 1,117 records in the table to be updated, I get a prompt-type message asking me if it ok to update >9,000 records. (This was the reason I decided to try a group by, I knew there was duplication in the second data source)..


So it begs the question, what is the update query doing, obviously a single row is being updated more than one time. I am guessing that the information in the other fields of EACH encounter of Tbl_Matched_Sites.GIB_SITE_DB = TblSites.SITE_DB will be written, but only the information in the last encounter will be left by the time the query runs...?

UPDATE Tbl_Matched_Sites 

LEFT  JOIN 

TblSites

on Tbl_Matched_Sites.GIB_SITE_DB = TblSites.SITE_DB

SET 

Tbl_Matched_Sites.GIB_Plant_Name= TblSites.Site_Station_Name,
Tbl_Matched_Sites.GIB_Phys_Addr_1 = TblSites.ADDRESS_LINE1, 
Tbl_Matched_Sites.GIB_Phys_City = TblSites.CITY, 
Tbl_Matched_Sites.GIB_Phys_zip = TblSites.STATE





More information about the AccessD mailing list