Solving the Operation Must Use An Updateable Query error
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, simply Response.Write(Environment.Username) to get 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.
Currently rated 4.73 by 51 people
Rate Now!
Date Posted:
30 April 2008 22:48
Last Updated:
17 August 2009 08:40
Posted by:
Mikesdotnetting
Total Views to date:
48250
Comments
30 December 2008 04:22 from 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
30 December 2008 06:46 from Mikesdotnetting
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.
27 February 2009 06:49 from Muraya
It worked but after restarting IIS in service.msc (Windows Xp)
29 April 2009 18:27 from Somnath Banerjee
hey buddy, thanx a lot. You solved my problem. THANK YOU again.
04 May 2009 16:52 from 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
04 May 2009 19:36 from Mikesdotnetting
@bhavesh
Follow the same steps as you did to solve it on the other machine.
08 May 2009 08:44 from 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
11 May 2009 07:08 from Mikesdotnetting
@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.
29 May 2009 16:52 from Bill Westgarth
Thanks a million for the tip.
Like you said "unbelievably cryptic".
How on earth did you fathom it out?
Regards, Bill Westgarth.
30 May 2009 21:31 from Mikesdotnetting
@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.
02 June 2009 10:29 from Trojan Horse
Another simple way in vista is: Run visual studio as "Administrator"(By right clicking on it)!! Thats it...
24 August 2009 22:29 from 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
04 September 2009 08:06 from Nagi
i coudnt find Security in folder property.i checked folder option also please help me to rectify the error
04 September 2009 23:15 from Mikesdotnetting
@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.
21 November 2009 01:59 from Max
What an awesome little trick! Thanks! I usually do trial and error to get the write permissions.
03 March 2010 15:35 from 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.
08 March 2010 18:04 from karan
thanks
22 April 2010 12:20 from 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.
17 May 2010 17:11 from 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?
17 May 2010 17:15 from Nigel
Quick question - you mention Response.Write(Environment.Username) above. I tried it and got the error " Object required: 'Environment' " Am I missing something?
17 May 2010 20:45 from Mikesdotnetting
@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.
23 September 2010 13:57 from ganesh
thanks it works
27 March 2011 07:21 from anvith
thanks mike it is really heplful
19 April 2011 03:05 from 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
20 April 2011 16:16 from Mikesdotnetting
@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.