Monday, September 29, 2008

Solving the "Operation must use an updateable query" error

This unbelievably cryptic 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, 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 and Vista, it is the NETWORK SERVICE account. However, if you have ASP.NET Impersonation enabled, the user account will be IUSR_machinename. 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 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.

No comments:

instantptr.com