Access Error Operation Must Use An 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 microsoft access operation must use an updateable query and policies of this site About Us Learn more about Stack Overflow
Access Operation Must Use An Updateable Query Linked Table
the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation operation must use an updateable query access 2013 Tags 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
Operation Must Use An Updateable Query Access 2007
only takes a minute: Sign 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 temporary tables, but I'm wondering if there's a better way. All the tables operation must use an updateable query access 2010 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 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
Operation Must Use An Updateable Query error 53 Comments 30 April 2008 22:48 4.68 (74 votes) ASP.NET 2.0 MS Access ADO.NET ASP.NET 3.5 The unbelievably cryptic Operation Must Use An Updateable Query error is the bane of
Operation Must Use An Updateable Query Access 2003
developers who are just starting out with Access and ASP.NET. You've done your code,
Operation Must Use An Updateable Query Access 2010 Windows 7
plopped your database file in the App_Data folder (or at least, you should have done), and try to run a page that operation must use an updateable query access insert INSERTs or UPDATEs records, and it all stops dead. This brief article explains the cause of the error, and the steps required to stop it recurring. When a Jet 4.0 database (the actual type of database http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access represented by your "Access" mdb file) is deployed in a multi-user environment, an .ldb file is created whenever the database is opened. The .ldb file contains details which include who has opened the file, and primarily serves to prevent opened records being written to by another user. In the context of an ASP.NET application, who the "user" is will depend on the platform: for XP Pro machines, the user is the ASPNET http://www.mikesdotnetting.com/article/74/solving-the-operation-must-use-an-updateable-query-error account. On Windows Server 2003, 2008 and Vista, it is the NETWORK SERVICE account. However, if you have ASP.NET Impersonation enabled, the default user account will be IUSR_machinename, or whichever account you have applied. With IIS 7.5, Application Pool Identities were introduced, which will result in your application running under an account named "IIS APPPOOL/name_of_application_pool", If you are unsure which account your ASP.NET application is running under, Environment.UserName will return it. To be able to create, write to and delete the required .ldb file, the relevant user needs MODIFY permissions on the folder that the .mdb file is in. To set this permission, right click on the App_Data folder (or whichever other folder you have put the mdb file in) and select Properties. Look for the Security tab. If you can't see it, you need to go to My Computer, then click Tools and choose Folder Options.... then click the View tab. Scroll to the bottom and uncheck "Use simple file sharing (recommended)". Back to the Security tab, you need to add the relevant account to the Group or User Names box. Click Add.... then click Advanced, then Find Now. The appropriate account should be listed. Double click it to add it to the Group or User Names box, then check the Modify option
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 Job Ways to https://www.experts-exchange.com/questions/28061195/Access-operation-must-use-an-updateable-query.html 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 http://www.pcreview.co.uk/threads/operation-must-use-an-updatable-query.4032253/ 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: 2013-03-12 Hi! I just created (what operation must 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. UPDATE SalesRepMonthlyOpenOrdersAggregateQ INNER JOIN SalesRepMonthlyT ON (SalesRepMonthlyOpenOrdersAggregateQ.Salesperson = SalesRepMonthlyT.Salesperson) operation must use 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, SalesRepMonthlyOpenOrdersAggregateQ WHERE SalesRepMonthlyOpenOrdersAggregateQ.Salesperson = alesRepMonthlyT.Salesperson) AND Sa
query and I got following message when I run it "operation must use an updatable query" The update query has one table and one query and I update the table. Can you please advise where I have look for the message? Your help is great appreciated, iccsi, Feb 17, 2011 #1 Advertisements John W. Vinson Guest On Thu, 17 Feb 2011 12:36:35 -0800 (PST), iccsi <> wrote: >I have an update query and I got following message when I run it > >"operation must use an updatable query" > >The update query has one table and one query and I update the table. >Can you please advise where I have look for the message? > >Your help is great appreciated, Please open the query in SQL view and post the SQL text here. There are quite a few reasons why a query might not be updateable; one common one is that any query which has a GROUP BY, or any query built on a GROUP BY query, is not updateable. -- John W. Vinson [MVP] Microsoft's replacements for these newsgroups: http://social.msdn.microsoft.com/Forums/en-US/accessdev/ http://social.answers.microsoft.com/Forums/en-US/addbuz/ and see also http://www.utteraccess.com John W. Vinson, Feb 17, 2011 #2 Advertisements Access Developer Guest The most common problem users in my user group have found is that they did not include the key field from the table they wanted to update. If you join the table and query on a key field, but the field you retrieve and display is from the query, you may not be able to update the table (even though the values are identical). But, as John says, there are quite a number of reasons. I found Help to be good on explaining why queries may be unupdateable (in some, but not all, versions of Access -- Help is one of the great variables between versions). Larry Linson Microsoft Office Access MVP "John W. Vinson"