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:
Last Updated:
Posted by:
Total Views to date: 198852

46 Comments

- 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

- 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.

- Muraya

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

- Somnath Banerjee

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

- 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

- Mike

@bhavesh

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

- 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

- 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.

- Bill Westgarth

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

- 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.

- Trojan Horse

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

- 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

- Nagi

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

- 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.

- Max

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

- 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.

- karan

thanks

- 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.

- 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?

- Nigel

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

- 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.

- ganesh

thanks it works

- anvith

thanks mike it is really heplful

- 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

- 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.

- 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 %)

- ljbonner

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

- Mansoor Ahmad Samar

Thanks a lot man. It really works. Thanks again

- 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!

- Mike

@eef,

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

- 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..

- Mikesdonetting

@jyotsana

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

- vimlesh kumar

Thank u very much

- H. Kooij

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

- SiewChin

THANK YOU VERY MUCH FOR POSTING THIS!!!

- manar

many thanks for you

- vivek

Thanks a ton ..:-))

- bert

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

- 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?

- 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

- Bryan

Thank you!! Life Saver

- ezzahraoui

thanks

- Sakthi

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

- Mike

@Sakthi,

Nope.

- esam emary

Trojan Horse
you are Genius
thnx from my heart

- sss

good