Jim Dettman
jimdettman at verizon.net
Thu Jun 9 07:33:49 CDT 2011
Actually, the number of tables really doesn't have anything to do with it. It's the type of joins, what key fields you include, and what fields are included in the update that determine if it's updateable or not. For example, tables joined on a non-unique index, no joined field (Cartesian query), use of SELECT DISTINCT, etc will always result in a non-updatable record set. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Wednesday, June 08, 2011 10:47 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Operation must use an updatable query 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 > > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com