Ms Access Vba 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
Ms Access Operation Must Use An Updateable Query
or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x operation must use an updateable query access 2013 Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it operation must use an updateable query access 2003 only takes a minute: Sign up Operation must use an updatable query. (Error 3073) Microsoft Access up vote 17 down vote favorite 4 On some Microsoft Access queries, I get the following message: Operation must use an updatable query. (Error
Operation Must Be An Updateable Query Access 2013
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) 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
Access Operation Must Use An Updateable Query Linked Table
Sklivvz♦ 20.5k2381152 asked Oct 4 '08 at 16:08 Knox 2,00872655 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 32.2k1177120 answered Oct 4 '08 at 21:12 David-W-Fenton 19.3k22947 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 "que
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 operation must use an updateable query vba or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x
Runtime Error 3073 Operation Must Use An Updateable Query
Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it operation must use an updateable query access 2010 windows 7 only takes a minute: Sign up INSERT INTO in MS Access 2010 SOMETIMES GETS ERROR: 3073 Operation must use an updateable query up vote 1 down vote favorite I get the ERROR: 3073 Operation must use an updateable query SOMETIMES, http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access while performing an INSERT statment. I have no problem on my windows 7 PC, but the person I am writing this for sometimes gets the error. She also has MS Access 2010 on Windows 7. As I said I have never got it on my PC, and she only gets it sometimes. The code will insert a number of rows and then through the error, and other times not through the erro at all. The error occurs if I have http://stackoverflow.com/questions/5201176/insert-into-in-ms-access-2010-sometimes-gets-error-3073-operation-must-use-an-u the code and data in one .mdb file or seperate files. Here a snippet of code: OrderHdrInsertStmnt = " INSERT INTO ORDER_HDR " _ & "(ORDER_ID, SOURCE_CODE, ORDER_DATE, SHIP_FNAME, SHIP_LNAME, SHIP_EMAIL, SHIP_COMP, SHIP_PHONE, SHIP_ADDR, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_CNTRY, " _ & " BILL_FNAME, BILL_LNAME, BILL_EMAIL, BILL_COMP, BILL_PHONE, BILL_ADDR, BILL_CITY, BILL_STATE, BILL_ZIP, BILL_CNTRY, " _ & " TAX, SHIPPING, TOTAL, MOD_DATE, INSERT_DATE) " _ & " VALUES (" _ & "'" & OrderId & "','" & SourceCode & "','" & Orderdate & "','" & ShipFName & "','" & ShipLName & "','" & ShipEmail & "','" & ShipComp & "','" & ShipPhone & "','" & ShipAddr & "','" & ShipCity & "','" & ShipState & "','" & ShipZip & "','" & ShipCntry _ & "','" & BillFName & "','" & BillLName & "','" & BillEmail & "','" & BillComp & "','" & BillPhone & "','" & BillAddr & "','" & BillCity & "','" & BillState & "','" & BillZip & "','" & BillCntry _ & "','" & OrderTax & "','" & OrderShipping & "','" & OrderTotal & "','" & ImportDate & "','" & ImportDate & "');" then I use dbsCurrent.Execute OrderHdrInsertStmnt, dbFailOnError Any assistance would be great! ms-access vba share|improve this question edited Mar 5 '11 at 7:16 Tony Toews 7,02511324 asked Mar 5 '11 at 2:15 Gary 612 Are all of those fields VarChar/Strings? –NoAlias Mar 5 '11 at 2:24 I have a mixture. there
help? Post your question and get tips & solutions from a community of 418,595 IT Pros & Developers. It's https://bytes.com/topic/access/answers/655652-operation-must-use-updateable-query quick & easy. "Operation must use an updateable query" P: 5 Mike https://msdn.microsoft.com/en-us/library/bb223637(v=office.12).aspx D OS: Windows XP Professional Microsoft Access 2003 I am trying to update a table in my DB from a tempory table. I need to ensure that if records in the main table match records in the temp table (on the keys) they are replaced/updated by the new operation must data in the temp table. the vba code i am running is below: Expand|Select|Wrap|Line Numbers DimmySQLAsString mySQL="UPDATEtab_Org" mySQL=mySQL&"SETOrg_Name=(SELECTtemp_TabOrg.Org_NameFROMtemp_TabOrgWHEREtemp_TabOrg.ORGKey=tab_Org.ORGKey)" mySQL=mySQL&"WHEREEXISTS(SELECTtemp_TabOrg.ORGKeyFROMtemp_TabOrgWHEREtemp_TabOrg.ORGKey=tab_Org.ORGKey);" DoCmd.SetWarningsFalse DoCmd.RunSQLmySQL DoCmd.SetWarningsTrue This is the update on just one field, when i get it working (fingers crossed) it will update all fields in the main table except the key field. When i run this code i get an updateable query the error "Runtime error 3073, Operation must use an updateable query." The general concessus in the forum is that this error is due to folder permissions etc. but I have checked these and I have the correct permissions, i have also tried to run the query on my personal computer and i get the same error. When i modified the query to not include the subquery in the SET part, instead just SET Org_Name = "some string" it works fine. Now if it was a permissions error i'm thinking that the query should still not work??? If anyone could help me fix the error or has a better way to achieve what i am trying to do it would be greatley appreciated Thanks Mike D May 31 '07 #1 Post Reply Share this Question 4 Replies Expert Mod 10K+ P: 14,534 MMcCarthy Hi Mike, Try this ... Expand|Select|Wrap|Line Numbers DimmySQLAsString mySQL="UPDATEtab_OrgINNERJOINtemp_TabOrg" mySQL=mySQL&"ONtab_Org.ORGKey=temp_TabOrg.ORGKey" mySQL=mySQL&"SETOrg_Name=temp_TabOrg.Org_Name" DoCmd.SetWarningsFalse DoCmd.RunSQLmySQL DoCmd.SetWarningsTrue Mary Jun 1 '07 #2 reply P: 5 Mike D Hi Mary, It works!!!!! thanks for the quick resp
resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft Student Partners ISV Startups TechRewards Events Community Magazine Forums Blogs Channel 9 Documentation APIs and reference Dev centers Samples Retired content We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. MSDN Library MSDN Library MSDN Library MSDN Library Design Tools Development Tools and Languages Mobile and Embedded Development .NET Development Office development Online Services Open Specifications patterns & practices Servers and Enterprise Development Speech Technologies Web Development Windows Desktop App Development TOC Collapse the table of content Expand the table of content This documentation is archived and is not being maintained. This documentation is archived and is not being maintained. Operation must use an updatable query. (Error 3073) You tried to run, open, or modify a query that is not updatable. Possible causes: You attempted to run a query that tried to update a field that cannot be updated. For example, you may have created the query in such a way that you tried to update a field on the one side of a one-to-many relationship. You tried to use the obsolete OpenQueryDef method on a query that is in a database opened for read-only access. The database is read-only for one of the following reasons: You used the OpenDatabase method or the Visual Basic Data control, and opened the database for read-only access. The database file has been defined as read-only in your network operating system. In a network environment, you do not have write privileges for the database file. Close the database, resolve the read-only condition, and then reopen it for read/write access. You do not have permission to make changes to the query. To change your permission assignments, see your system administrator or the query's creator. Community Additions Show: Inherited Protected Print Export (0) Print Export (0) Share IN THIS ARTICLE Is this page helpful? Yes No Additional feedback? 1500 characters remaining Submit Skip this Thank you! We appreciate your feedback. Dev centers Windows Office Visual Studio Microsoft Azure More... Learning resources Microsoft Virtual Academy Channel 9 MSDN Magazine Community Forums Blogs Codeplex Suppor