[AccessD] Operation must use an updatable query

Charlotte Foust charlotte.foust at gmail.com
Wed Jun 8 21:46:39 CDT 2011


John,
I've had those kinds of queries too and in the earlier version of Access,
you could still make them updateable.  It isn't entirely the number of
tables, but I haven't figured out the magic number.

Charlotte Foust

On Wed, Jun 8, 2011 at 12: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
>
>
>



More information about the AccessD mailing list