[AccessD] Operation must use an updatable query

Gary Kjos garykjos at gmail.com
Wed Jun 8 14:48:41 CDT 2011


Here is a link to a table of "when is a query update-able" that I
saved a favorite to a while back

http://msdn.microsoft.com/en-us/library/aa198446%28office.10%29.aspx

GK

On Wed, Jun 8, 2011 at 2:33 PM, jwcolby <jwcolby at colbyconsulting.com> wrote:
>> The rule is that a query with more than two tables joined is not
>> updateable.
>
> I'm glad I didn't know this because I have an updateable query with 12
> tables in it, many of them outer joins.  It is used as the query for a bound
> form.  Damned ugly it is too!  ;)
>
> And I do not recommend it.
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 6/8/2011 3:00 PM, Charlotte Foust wrote:
>>
>> The rule is that a query with more than two tables joined is not
>> updateable.  The outer join may be the reason you're getting an inflated
>> number of records.  Have you tried using an inner join?
>>
>> Charlotte Foust
>> On Wed, Jun 8, 2011 at 11:50 AM, William Benson (VBACreations.Com)<
>> vbacreations at gmail.com>  wrote:
>>
>>> 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
>>>
>>>
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>
>>>
>>> Website: http://www.databaseadvisors.com
>>>
>>>
>>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Gary Kjos
garykjos at gmail.com




More information about the AccessD mailing list