Solving the Operation Must Use An Updateable Query error

The unbelievably cryptic Operation Must Use An Updateable Query error is the bane of developers who are just starting out with Access and ASP.NET. You've done your code, plopped your database file in the App_Data folder (or at least, you should have done), and try to run a page that 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 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 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. 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 in the permissions. That's it. You are done.

Note: this fix will also solve "The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data" errors.

Date Posted: Wednesday, April 30, 2008 10:48 PM
Last Updated: Wednesday, March 21, 2012 8:36 AM
Posted by: Mikesdotnetting
Total Views to date: 165408

45 Comments

Tuesday, December 30, 2008 4:22 AM - Aaron

Hi Mike:

Thanks for this post. I'll give it a shot to see if it resolves a similar issue on our application running on Vista and accessing the data.mdb file. The app. is coded in C++ so hopefully your solution will work for us as well since nothing else has been effective thus far.

Regards,
Aaron

Tuesday, December 30, 2008 6:46 AM - Mike

Hi Aaron,

The error is thrown by the underlying Jet Oledb provider, which doesn't know or care which language you are using. So long as you can identitfy the correct user account to apply the permissions to, you whould be able to resolve the issue.

Friday, February 27, 2009 6:49 AM - Muraya

It worked but after restarting IIS in service.msc (Windows Xp)

Wednesday, April 29, 2009 6:27 PM - Somnath Banerjee

hey buddy, thanx a lot. You solved my problem. THANK YOU again.

Monday, May 4, 2009 4:52 PM - bhavesh

Thanks for giving this solution and I solve on my II but i can't understand how to solve on my web server please replay me on my email id how to solve this problem on my web server??

Thanks

Monday, May 4, 2009 7:36 PM - Mike

@bhavesh

Follow the same steps as you did to solve it on the other machine.

Friday, May 8, 2009 8:44 AM - matrostik

i am using Windows 7 and set permissions to NETWORK SERVICE account everything work ok but .ldb file is not created and if exception is throwing the .mdb file stay locked until i kill IIS Worker process

Monday, May 11, 2009 7:08 AM - Mike

@Mastrostik

I believe that the user account in Windows 7 is IUSR_machinename. this is the one that need MODIFY permissions on the relevant folder.

Friday, May 29, 2009 4:52 PM - Bill Westgarth

Thanks a million for the tip.
Like you said "unbelievably cryptic".
How on earth did you fathom it out?
Regards, Bill Westgarth.

Saturday, May 30, 2009 9:31 PM - Mike

@Bill

I can't take the credit for that. The error message is returned from the Jet provider, and that's been the case since way before I even started web development. When I first encountered the error message, someone in a Usenet news group gave me the answer. Since it's still a common question, I thought I'd put the full answer somewhere where I could reference it easily. Hence the article.

Tuesday, June 2, 2009 10:29 AM - Trojan Horse

Another simple way in vista is: Run visual studio as "Administrator"(By right clicking on it)!! Thats it...

Monday, August 24, 2009 10:29 PM - RK

Thanks a lot dude! I was breaking my head since morning to solve this problem. You helped me out in 2 mins!
Thank you

Friday, September 4, 2009 8:06 AM - Nagi

i coudnt find Security in folder property.i checked folder option also please help me to rectify the error

Friday, September 4, 2009 11:15 PM - Mike

@Nagi

I can't add anything more than I have already covered in the article. Perhaps you should read it again. The answer is there.

Saturday, November 21, 2009 1:59 AM - Max

What an awesome little trick! Thanks! I usually do trial and error to get the write permissions.

Wednesday, March 3, 2010 3:35 PM - Chris

One thing you should try if you can't see the security tab of file/folder properties is going to Tools-->Folder Options and looking on the View tab at the bottom of the list for "Use simple file sharing." Uncheck this option, apply changes, and check again.

Monday, March 8, 2010 6:04 PM - karan

thanks

Thursday, April 22, 2010 12:20 PM - Barrie Lloyd

Unfortunately most commentaries on this subject, including those on Microsoft's own website concentrate on the issue of permissions. Whilst in many cases this may be the cause of the problem, the concentration on this aspect distracts from other possible causes, such as Access's difficulty in dealing adequately with nested queries.

Monday, May 17, 2010 5:11 PM - Nigel

A HUUUGGGGGEEEEEE well done to Barry LLoyd. He is the only person I have seen (on many forums) to hit the nail on the head. I have run into this error countless times and it has ALWAYS been because of using nested queries to update tables and NEVER permissions. Having said that the only way I have managed a workaround is dump the output from my query into a 'temp' table and use a second query to pick up that data and update my table. Anyone got any better solutions?

Monday, May 17, 2010 5:15 PM - Nigel

Quick question - you mention Response.Write(Environment.Username) above. I tried it and got the error " Object required: 'Environment' " Am I missing something?

Monday, May 17, 2010 8:45 PM - Mike

@Nigel,

While Barrie is absolutely correct about one of the alternative causes of this error, I have to say that I have never seen it caused by nested queries in any newsgroup or forum post I have responded to, and that numbers in the thousands. It has always been as a result of permissions.

Having said that, it may be a more common source of the error in Access development forums. I wouldn't know. I have only ever used Access in the context of a web application which is why I concentrated on the permissions problem. So I published Barrie's comment because it may help someone.

You've run into the error countless times? I hope you mean you have seen it from other people rather than caused it yourself ;o) You'd think you would learn!

Environment is a class in the .NET System namespace. Are you referencing that namespace? Are you sure you are actually using ASP.NET and not classic ASP? The 'Object Required' error usually comes from VBScript, which is classic ASP.

Thursday, September 23, 2010 1:57 PM - ganesh

thanks it works

Sunday, March 27, 2011 7:21 AM - anvith

thanks mike it is really heplful

Tuesday, April 19, 2011 3:05 AM - Vijay

In extended properties of the connection string, I set IMEX=1. Removing the IMEX settings ensured that this error did not come.

Environment: Windows 7, Excel 2010, Asp.net 4

Wednesday, April 20, 2011 4:16 PM - Mike

@vijay,

This article covers the error message as it applies to Access databases in a web environment. The IMEX setting in Extended Properties of an Excel connection string is to do with how the provider sees "intermixed" data, and tells it to treat it as text if set to 1.

I debated deleting your comment in case it causes confusion, but decided it would be better to publish it and some clarification.

Wednesday, May 30, 2012 3:25 AM - Grandpa_pip

Thank you.... after 1/2 hour and bleary eyes I found your work around. Trying to learn and woking on a local server ( IIS) and almost trashed the attempt. Will bookmark and pass the word. Now on to my next migrane... revising the look of the page %)

Monday, June 11, 2012 4:51 AM - ljbonner

Thank you. As a complete tyro I spent 3 hours trying to crack this. Your page solved it.

Wednesday, July 11, 2012 8:37 PM - Mansoor Ahmad Samar

Thanks a lot man. It really works. Thanks again

Friday, July 27, 2012 9:52 AM - eef

thanks. very clear and logic explanation. however, all users (administration as well as myself) have full permission.
i never encountered the problem before either (it happened while wanting to run an update query in access. i read in obe of the comments something about C++. I think i might have deleted the Microsoft Visual basic C++ or soemthing programme yesterday because i thought it was one of those stupid useless by accident downloaded programmes. might i been mistaken and this be part of the problem?

mant thanx!

Friday, July 27, 2012 3:40 PM - Mike

@eef,

This article only covers the problem arising in the context of using an Access database in a web application.

Tuesday, October 2, 2012 7:10 AM - jyotsana

i am not using 'jet 4.0', but i am using 'ACE 12.0' database and getting the same error when running my program on IIS.
Please help .... it's urgent..

Thursday, October 4, 2012 7:41 AM - Mikesdonetting

@jyotsana

The same error results from the same issue - lack of appropriate permissions on the folder.

Sunday, October 14, 2012 8:28 AM - vimlesh kumar

Thank u very much

Tuesday, November 6, 2012 1:23 AM - H. Kooij

Great, it directly solved all the problems i had with my database. Thnx!

Thursday, November 15, 2012 4:02 PM - SiewChin

THANK YOU VERY MUCH FOR POSTING THIS!!!

Sunday, May 12, 2013 11:48 PM - manar

many thanks for you

Thursday, June 27, 2013 9:28 AM - vivek

Thanks a ton ..:-))

Wednesday, August 14, 2013 5:57 PM - bert

almost 5 years later and this is still helping people - me!
thanks for the post!

Thursday, August 15, 2013 8:11 AM - Graeme

Hey there ive tried to change the permissions but still havent find luck can yous perhaps help me everything is working locally but when i upload it to the internet what should do what permissions should i choose?

Thursday, August 29, 2013 10:33 AM - Chris Sotshantsha

Guys i encountered the problem with Deloitte Peromnes software. I have to say that this solution worked but also you need to give the user full control to the folder that contains the database. Thank you guys

Wednesday, September 4, 2013 4:01 PM - Bryan

Thank you!! Life Saver

Sunday, April 20, 2014 10:56 PM - ezzahraoui

thanks

Monday, May 19, 2014 9:40 AM - Sakthi

I am having this update problem since i have changed my registry settings as TypeGuessRows =0. Any idea?

Monday, May 19, 2014 10:33 AM - Mike

@Sakthi,

Nope.

Tuesday, October 28, 2014 9:34 PM - esam emary

Trojan Horse
you are Genius
thnx from my heart
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.