Access Operation Must Use An Updateable Query. Error 3073
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 Stack Overflow the company Business Learn more about hiring developers or posting ads with ms access error operation must use an updateable query us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack
Microsoft Access Operation Must Use An Updateable Query
Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign
Access Operation Must Use An Updateable Query Linked Table
up Operation must use an updatable query. (Error 3073) Microsoft Access up vote 16 down 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
Operation Must Use An Updateable Query Access 2013
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) 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 operation must use an updateable query access 2007 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 CLOG.NEXTDUE IS NULL), and the only reason why it does not work is that the query used to compute new value of CLOG.NEXDUE is "too complex". Unbelievable!! I am glad we switched from Jet to SQL Server!!! :-)) –Yarik Nov 3 '08 at 8:42 5 I don't believe your assertion that the SQL you quote could ever produce the "query too complex" error in Jet. You are either mistaken, or for some reason I can't fathom, spreading misinformation about Jet. &ndas
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 Stack Overflow the company Business Learn more about operation must use an updateable query access 2010 hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask operation must use an updateable query access 2003 Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. operation must use an updateable query access 2010 windows 7 Join them; it only takes a minute: Sign up Operation must use an updatable query. (Error 3073) Microsoft Access up vote 2 down vote favorite I have written this query: UPDATE tbl_stock1 SET tbl_stock1.weight1 = ( select (b.weight1 - http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access c.weight_in_gram) as temp from tbl_stock1 as b, tbl_sales_item as c where b.item_submodel_id = c.item_submodel_id and b.item_submodel_id = tbl_stock1.item_submodel_id and b.status <> 'D' and c.status <> 'D' ), tbl_stock1.qty1 = ( select (b.qty1 - c.qty) as temp1 from tbl_stock1 as b, tbl_sales_item as c where b.item_submodel_id = c.item_submodel_id and b.item_submodel_id = tbl_stock1.item_submodel_id and b.status <> 'D' and c.status <> 'D' ) WHERE tbl_stock1.item_submodel_id = 'ISUBM/1' and tbl_stock1.status <> 'D'; I got this error message: Operation must use an updatable query. http://stackoverflow.com/questions/1231350/operation-must-use-an-updatable-query-error-3073-microsoft-access (Error 3073) Microsoft Access But if I run the same query in SQL Server it will be executed. Thanks, dinesh ms-access vba access-vba jet share|improve this question edited Aug 5 '09 at 14:34 BIBD 9,0082059101 asked Aug 5 '09 at 5:30 Dinesh add a comment| 5 Answers 5 active oldest votes up vote 2 down vote I'm quite sure the JET DB Engine treats any query with a subquery as non-updateable. This is most likely the reason for the error and, thus, you'll need to rework the logic and avoid the subqueries. As a test, you might also try to remove the calculation (the subtraction) being performed in each of the two subqueries. This calculation may not be playing nicely with the update as well. share|improve this answer edited Aug 5 '09 at 6:24 answered Aug 5 '09 at 6:04 Ben Griswold 9,649114556 Depends on the subquery. Subqueries in WHERE clauses do not have that effect. Subqueries in the FROM may or may not, depending on various issues. The main issue is whether or not Jet/ACE can resolve the relationship type so that it is unambiguously told which single record to update. One thing to try if you're running the SQL in Access itself is the DISTINCTROW predicate, which can sometimes make nonupdatable queries updatable. –David-W-Fenton Aug 5 '09 at 18:30 "The main issue is whether or not Jet
One games Xbox 360 games PC https://support.microsoft.com/en-us/kb/175168 games Windows games Windows phone games Entertainment All Entertainment Movies & TV Music Business & Education Business Students & http://www.utteraccess.com/forum/Runtime-Error-3073-t1917945.html educators Developers Sale Sale Find a store Gift cards Products Software & services Windows Office Free downloads & security operation must 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 operation must use 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
Custom Search UtterAccess Forums>Microsoft® Access>Access Queries Runtime Error 3073 Forum HomeSearchHelpUA Messages|-- UtterAccess.com NewsAccess Knowledge Center|-- Access Code Archive|-- Access Knowledgebase / FAQ|-- Access TutorialsMicrosoft® Access|-- Local Access User Groups (AUGs)|-- Interface Design|-- Access Q and A|-- Access Tables + Relationships|-- Access Queries|-- Access Forms|-- Access Reports|-- Access Macros|-- Access Modules|-- Access Date + Time|-- Access Errors + Error Handling|-- Access Built-in Functions|-- Access Searching + Data Mining|-- Access Records|-- Access Security|-- Access Automation|-- Access Database Container|-- Tool bars, Menu bars + Ribbon|-- Access Services (Web Databases)|-- Access Data Access Pages (Deprecated)|-- Access Data Projects - ADP|-- Access Runtime, Packaging & DeploymentAnd More...|-- Microsoft SQL Server|-- SQL Server Reporting Services|-- Microsoft SharePoint|-- Microsoft Internet Explorer|-- Visual Basic 6 and Earlier|-- Visual Basic 2003 and Later|-- ASP and VBScript|-- Web Development|-- PHP, Perl, MySQL and Postgres|-- Other Database Engines|-- BI Tools and Applications|-- JAVA and C#|-- GraphicsMicrosoft® Office|-- Microsoft Excel|-- Microsoft Excel FAQs|-- Microsoft Word|-- Microsoft Outlook|-- Microsoft PowerPoint|-- Microsoft FrontPage/Exp. Web|-- Other Microsoft ProductsPC|-- Network Issues|-- Q & A - Hardware|-- Q & A - Software|-- Q & A - Other PC|-- Virus + Security DiscussionUtterAccess Odds and Ends|-- General Chat|-- Q & A - UtterAccess.com Forums|-- Q & A - Access Wiki Options trimarkView Member Profile Dec 28 2009, 06:44 PM Post#1Posts: 64Joined: 13-October 07From: Wisconsin Dells, WII have a database that runs smooth in 2003. Trying it in 2007, and the SqlUPDATE tblproductsold SET Cost = '7.99', SuggestedSell = '8.30', Jobber = '6.39' WHERE PartID = 'ABC00004'"gives an error of 3073 'must be an updateable query. I'm guessing that more of this problem will show up."tblproductsold" is a linked table, not a query.Since I'm not familiar with 20