Access Error Operation Must Use Updateable Query
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about microsoft access operation must use an updateable query Stack Overflow the company Business Learn more about hiring developers or posting ads with
Access Operation Must Use An Updateable Query Linked Table
us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is
Operation Must Use An Updateable Query Access 2013
a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Operation must use an updatable query. (Error 3073) Microsoft Access up vote 16 down
Operation Must Use An Updateable Query Access 2007
vote favorite 4 On some Microsoft Access queries, I get the following message: Operation must use an updatable query. (Error 3073). I work around it by using temporary tables, but I'm wondering if there's a better way. All the tables involved have a primary key. Here's the code: UPDATE CLOG SET CLOG.NEXTDUE = ( SELECT H1.paidthru FROM CTRHIST as H1 WHERE H1.ACCT = clog.ACCT AND H1.SEQNO = ( SELECT MAX(SEQNO) operation must use an updateable query access 2010 FROM CTRHIST WHERE CTRHIST.ACCT = Clog.ACCT AND CTRHIST.AMTPAID > 0 AND CTRHIST.DATEPAID < CLOG.UPDATED_ON ) ) WHERE CLOG.NEXTDUE IS NULL; ms-access share|improve this question edited Oct 4 '08 at 21:46 Sklivvz♦ 20.4k2381152 asked Oct 4 '08 at 16:08 Knox 1,99872655 add a comment| 20 Answers 20 active oldest votes up vote 20 down vote accepted Since Jet 4, all queries that have a join to a SQL statement that summarizes data will be non-updatable. You aren't using a JOIN, but the WHERE clause is exactly equivalent to a join, and thus, the Jet query optimizer treats it the same way it treats a join. I'm afraid you're out of luck without a temp table, though maybe somebody with greater Jet SQL knowledge than I can come up with a workaround. BTW, it might have been updatable in Jet 3.5 (Access 97), as a whole lot of queries were updatable then that became non-updatable when upgraded to Jet 4. -- share|improve this answer edited Oct 24 '12 at 13:53 Anirudh Ramanathan 31.9k1177120 answered Oct 4 '08 at 21:12 David-W-Fenton 19.2k22947 Thanks for the explanation. –Knox Oct 5 '08 at 17:02 1 So we have a pretty trivial update statement (UPDATE CLOG SET CLOG.NEXDUE = ... WHERE CLO
One games Xbox 360 games PC operation must use an updateable query access 2003 games Windows games Windows phone games Entertainment All operation must use an updateable query access 2010 windows 7 Entertainment Movies & TV Music Business & Education Business Students & operation must use an updateable query access insert educators Developers Sale Sale Find a store Gift cards Products Software & services Windows Office Free downloads & security http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access Internet Explorer Microsoft Edge Skype OneNote OneDrive Microsoft Health MSN Bing Microsoft Groove Microsoft Movies & TV Devices & Xbox All Microsoft devices Microsoft Surface All Windows PCs & tablets PC accessories Xbox & games Microsoft Band Microsoft https://support.microsoft.com/en-us/kb/175168 Lumia All Windows phones Microsoft HoloLens For business Cloud Platform Microsoft Azure Microsoft Dynamics Windows for business Office for business Skype for business Surface for business Enterprise solutions Small business solutions Find a solutions provider Volume Licensing For developers & IT pros Develop Windows apps Microsoft Azure MSDN TechNet Visual Studio For students & educators Office for students OneNote in classroom Shop PCs & tablets perfect for students Microsoft in Education Support Sign in Cart Cart Javascript is disabled Please enable javascript and refresh the page Cookies are disabled Please enable cookies and refresh the page CV: {{ getCv() }} English (United States) Terms of use Privacy & cookies Trademarks © 2016 Microsoft
Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time https://www.experts-exchange.com/questions/28061195/Access-operation-must-use-an-updateable-query.html Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > Access - "operation must use an updateable query" Want to Advertise Here? Solved Access - "operation must use an updateable query" Posted on 2013-03-12 MS Access 5 Verified Solutions 39 Comments 10,666 Views Last Modified: operation must 2013-03-12 Hi! I just created (what I thought was) a simple query. I want it to take data from another query, and update the corresponding fields (based on primary keys) in a table. However, when I try to run it, I get an error - "operation must use an updateable query". Does anyone know why this might be? Here's my SQL, in case you need it. operation must use UPDATE SalesRepMonthlyOpenOrdersAggregateQ INNER JOIN SalesRepMonthlyT ON (SalesRepMonthlyOpenOrdersAggregateQ.Salesperson = SalesRepMonthlyT.Salesperson) AND (SalesRepMonthlyOpenOrdersAggregateQ.Month = SalesRepMonthlyT.Month) AND (SalesRepMonthlyOpenOrdersAggregateQ.Year = SalesRepMonthlyT.Year) SET SalesRepMonthlyT.OpenOrderPrice = [sumofprice]; Select all Open in new window Thanks!!! 0 Question by:etech0 Facebook Twitter LinkedIn Google LVL 46 Active today Best Solution byDale Fye (Access MVP) You might be able to create a temporary table (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_9753-Creating-and-using-Temporary-Tables-in-Microsoft-Access.html) from your grouping query, Go to Solution 39 Comments LVL 30 Overall: Level 30 MS Access 28 Message Active today Assisted Solution by:hnasr2013-03-12 Convert the query to a select query. If you can add records then the query is updateable, and then update query works. 0 LVL 10 Overall: Level 10 MS Access 4 Message Author Comment by:etech02013-03-12 I can't add records. Why would that be? Should I change the join type, or something? 0 LVL 6 Overall: Level 6 MS Access 4 Message Assisted Solution by:PJBX2013-03-12 From time to time, I run across this issue with Access and update queries. I always get around it by going into SQL View and change your query to: UPDATE SalesRepMonthlyT SET SalesRepMonthlyT.OpenOrderPrice = SalesRepMonthlyOpenOrdersAggregateQ. [sumofprice] FROM SalesRepMonthlyT, SalesRepMonthlyOpenOrders