What is “Operation Must Use an Updateable Query” and How Do You Fix It?
By Joseph Maabo
New developers are usually faced with the “operation must use an updateable” query error when trying to update records in a database through a local or a web (online) application.
Contents
Possible reasons for the occurrence of the error
There are several reasons that the “Operation must use an updateable query” might occur. In this particular article, we will be looking at the error specifically in relation to MS Access.
Some of the reasons are:
- The table doesn’t have a primary key field.
- You don’t have permission to access the “App_data” folder.
- The MS-Access file is in a read-only folder, such as the Program Files folder.
- Using the UPDATE query that includes a JOIN statement.
- Your web application does not have “write permission” in the folder where the database is located.
- The MS-Access file has a “Read Only” attribute.
- Another database connection is already open.
While this list of reasons is not exhaustive, they do fall into one of the following three categories:
- Database configuration
- Access restriction to database location
- Query statement
Let us work through these three categories and show you how to solve the issue.
Errors due to database configuration
The table doesn’t have a primary key
A primary key is a special column/field (or combination of columns) in a table, which uniquely identifies all table records. A primary key’s main features are:
- It must contain a unique value for each row of data.
- Cannot contain null values.
- It has an associated index for fast query performance.
Although it isn’t necessary to have a primary key in a table, it is advisable to do so when dealing with many related tables in a database. At times – especially for complex queries – when a primary key is not defined, it can lead to the “Operation must be an updateable query” error.
The MS-Access file has a Read-only attribute.
If the access file (.mdb extension or .accdb for a newer version of MS Access) has a read Read-only attribute, then you can’t edit it. To enable updating and inserting of records you need to remove this Read-only attribute. You can do that in Windows Explorer. Locate the MS-Access file, right click on it and select properties. Uncheck the Read-only checkbox (see images bellow).
Access Restriction to Database Location
Errors due to restricted access to file location
The file might have a Read-only attribute unchecked, but the error message still appears. This may be because the file is located in a folder that the user/application doesn’t have access permission. This is the case with sensitive folders like Program Files or App_data (mostly for online access). If you are logged onto the computer with a user account that is in the “Users” group, this would restrict changes on any files that are not in the “My Documents” folder. You must use an account that is part of the “Power Users” group and make sure this user group allows full rights to the folder containing the MS-Access file. This may also occur if the database resides in a network location that the current user does not have permission to change. You may need to have a network administrator make these changes to the user account.
To solve that, do the following:
- Identify the folder containing the MS-Access file,
- Right click on it and select properties
- [1] Select the Security tab > [2] Click on the Edit button and > [3] check give full control.
Nevertheless, it is advisable to move the file to a folder where your application has write permission. For the App_data folder, if your website is on the internet you must use a Control Panel setting (such as C-Panel or Plesk) to set permission.
Errors due to the query
A query is a request for data or information from a database table or combination of tables. This data may be generated as results returned by Structured Query Language (SQL) or as pictorials, graphs or complex results, e.g., trend analyses from data-mining tools. A poorly written query can also generate the “Operation must be an updateable query” error. That is why it is important to have a good knowledge of the SQL Language before attempting it. For example, when you have joins and are not including all of the primary keys necessary to update back to tables in a data bound form. our original DISTINCT destroys information about keys, so if it was bound to a form, the form would not be able to save back.
On top of this, if your database is already opened, you can’t open another connection and update records. So it is important to check your code to see if you have made provisions to disconnect from the database after executing a query.
In case you don’t want to disconnect from the database, then consider using the same connection instead of creating a new one.
Hope this article has brought some light on your worries. Happy coding.