Access Runtime 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 runtime error 3073 operation must use an updateable query policies of this site About Us Learn more about Stack Overflow the ms access runtime error company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags
Ms Access Runtime Error 91
Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only
Ms Access Runtime Error 5
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 - 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' ms access runtime error 13 type mismatch ), 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. (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 thi
Posts Search Community Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag
Ms Access Runtime Error 2105
Search Advanced Search Find All Thanked Posts Go to Page... ms access runtime error 3061 Thread Tools Rating: Display Modes 09-14-2011, 12:45 AM #1 bulbisi Newly Registered User Join ms access runtime error 2465 Date: Jan 2011 Location: Brussels, Belgium Posts: 51 Thanks: 8 Thanked 1 Time in 1 Post Run-time error 3073 Operation must use an updateable query Hello all, it http://stackoverflow.com/questions/1231350/operation-must-use-an-updatable-query-error-3073-microsoft-access looks like i have an issue. I wanted to avoid a kind of temporary table as the DB I'm creating is quite heavy and complex already. Before: 1 Append Query to Table 1 Update Query to fill a new table from the previously created table It worked like a charm but was way to heavy and http://www.access-programmers.co.uk/forums/showthread.php?t=215498 slow to control (more than 22.000 records every week just for this table) So I wanted to use a Select Query instead the first step After: 1 Select Query 1 Update Query to fill a new table from the previous Select Query Then of course I got an error 3073. Note: The update query is running through VBA. Note: I might try a nested Select into the update Query, but i really don't know how to use it. Note: Access 2010 Code for Select Query: Code: SELECT T_CE_TOBE.DebtorNumber, Max(DateDiff("m",[EndOfMonthDate],DateAdd("m",[tblco.Ter],[tblCO.StsLIVDat]))) AS ResidualMonths FROM T_CE_TOBE INNER JOIN tblCO ON T_CE_TOBE.DebtorNumber = tblCO.DebNum GROUP BY T_CE_TOBE.DebtorNumber; Code for Update Query (in VBA): Code: UpdateMonths = "UPDATE T_CE_TOBE LEFT JOIN Q_CE_TOBE_periods ON T_CE_TOBE.DebtorNumber = Q_CE_TOBE_periods.DebtorNumber SET T_CE_TOBE.ResidualMonths = [Q_CE_TOBE_periods].[ResidualMonths]" DoCmd.RunSQL UpdateMonths Any help is really appreciated and thanks a lot in advance bulbisi View Public Profile Find More Posts by bulbisi 09-14-2011, 10:18 AM #2 PNGBill Win XP MS Access 2010 Join Date: J
help? Post your question and get tips & solutions from a community of 418,417 IT Pros & Developers. https://bytes.com/topic/access/answers/655652-operation-must-use-updateable-query It's quick & easy. "Operation must use an updateable query" P: 5 Mike D OS: Windows XP Professional Microsoft Access 2003 I am trying to update a table in my http://www.ozgrid.com/forum/showthread.php?t=34599 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 runtime error new 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 access runtime error code i get 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 work
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel | Excel Web Pages | Excel Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read Quick Links Today's Posts What's New? Advanced Search Forum OTHER SOFTWARE APPLICATIONS Excel and/or Access Help Error 3073: Must use updateable query Excel Training / Excel Dashboards Reports If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here to view the relaunched Ozgrid newsletter. Results 1 to 2 of 2 Thread: Error 3073: Must use updateable query Thread Tools Show Printable Version Search Thread Advanced Search June 1st, 2005 #1 ski52 View Profile View Forum Posts Member Join Date 24th March 2005 Posts 95 Error 3073: Must use updateable query When attempting to run the following SQL code in VBA: VB: DoCmd.RunSQL "UPDATE temp SET temp.[Project ID] = (SELECT Projects.[Project ID] FROM Projects " & _ "WHERE temp.APPN = Projects.APPN " & _ "AND temp.PROJECT = Projects.PROJECT " & _ "AND temp.ACTIVITY = Projects.ACTIVITY) " & _ "WHERE (Exists (SELECT Projects.[Project ID] FROM Projects " & _ "WHERE temp.APPN = Projects.APPN " & _ "AND temp.PROJECT = Projects.PROJECT " & _ "AND temp.ACTIVITY = Projects.ACTIVITY)=True)" I get the following error: Run-time error '3073': Operation must use an updateable query. I alter the temp table prior to this code to add a primary key, but the error still comes up. Any help? Excel Video Tutorials / Excel Dashboards Reports Reply With Quote June 2nd, 2005 #2 RowanS View Profile View Forum Posts Senior Member Join Date 12th May 2005 Location Land of long white clouds and flightless birds. Posts 140 Re: Error 3073: Must use updateable query Hi Ski I suspect your problem lies in this query VB: SELECT Projects.[Project ID] FROM Projects " & _ "WHERE temp.APPN = Projects.APPN " & _ "AND temp.PROJECT = Projects.PROJECT " & _ "AND temp.ACTIVITY = Projects.ACTIVITY returning more than on record. I suggest you use this query to build a recordset and the use a For Next loop to do the update. Cheers RowanS Excel Video Tutorials / Excel Dashboards Reports Reply With Quote Quick