[AccessD] Operation must use an updatable query

Gary Kjos garykjos at gmail.com
Wed Jun 8 14:32:06 CDT 2011


I have had cases were I will use a group by query to make a temporary
table and then use that temporary table to join to the table I need to
update.

GK

On Wed, Jun 8, 2011 at 2:22 PM, William Benson (VBACreations.Com)
<vbacreations at gmail.com> wrote:
> OK, tried that on the latest query and no difference in the number of rows which got updated. I have given up trying to join on more than one table in a update query.
>
> Thank you.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
> Sent: Wednesday, June 08, 2011 2:58 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Operation must use an updatable query
>
> And one more suggestion: try to use UPDATE DISTINCTROW instead of just UPDATE.
> Asger
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Asger Blond
> Sendt: 8. juni 2011 20:32
> Til: 'Access Developers discussion and problem solving'
> Emne: Re: [AccessD] Operation must use an updatable query
>
> Did you try to make the subquery a separate query and then join this to Tbl_Matched_Sites in the update query?
> I vaguely remember a situation where I had an update query with a subquery which Access considered non updatable - and as far as I remember making the subquery a separate query got it to work.
> Asger
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af William Benson (VBACreations.Com)
> Sendt: 8. juni 2011 19:43
> Til: 'Access Developers discussion and problem solving'
> Emne: [AccessD] Operation must use an updatable query
>
> Ahh--- well, Asger, thank you for your correction - that was merely a typo (corrected below).
>
> Is it so that there is no way to improve syntax of a group by query being used to update records in a table without dealing with primary keys in both sources?
>
> I have tried this many ways, eventually settling on First() thinking this would eliminate redundancies, which I thought might have been the issue.
>
> Actually, there were far MORE redundant records when I was joining table to table instead of table to group-by-query.
>
>
>
>
>
>
> ....
> I am getting a message that "operation must use an updatable query" from attempting to run this SQL. The reason I decided to try to join on a query is that TblSites has tremendous duplication in it, and I would update based on the TblSites table and it would be reported that many times more rows in Tbl_Matched_Sites were about to be updated than in fact there even were in the table. So I thought I might be able to improve performance by changing the join to a group by query. Is there a way to get this to work?
>
> UPDATE Tbl_Matched_Sites INNER JOIN
>
> (Select
>          First(SITE_DB) as FirstOfSITE_DB,
>          FirstOf(Site_Station_Name) as FirstOf Site_Station_Name,
>          First(Address_Line1) as FirstOfAddress_Line1,
>          First(City) as FirstOfCity,
>          First(State) as FirstOfState
>
> From
>          TblSites
>
> Group By
>          Site_Station_Name,
>          Address_Line1,
>          city,
>          State  )
>
> as   MainInfo  ON
>
>
> Tbl_Matched_Sites.GIB_SITE_DB = MainInfo.FirstOfSITE_DB
>
> SET
>
> Tbl_Matched_Sites.GIB_Phys_Addr_1 = MainInfo.[ FirstOfAddress_Line1], Tbl_Matched_Sites.GIB_Phys_City = MainInfo.[ FirstOfCity], Tbl_Matched_Sites.GIB_Phys_State = MainInfo.[ FirstOfState]
>
> --
> 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
>
>
> --
> 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
>



-- 
Gary Kjos
garykjos at gmail.com




More information about the AccessD mailing list