In this post, i have figured out some reasons of why your MS access update query not working. Go through all the reasons very attentively as these will help you to fix update query not working issue easily and smartly.
What Is Update Query In MS Access?
An update query is such a type of action query that is basically used to change a set of records as per the criteria you set into it. As this is a fundamental part of the relational database so you can easily make modification in numerous number of records simultaneously
Having the right knowledge of Update Queries you can improve the performance of your application.
Update Queries help you to modify the field data or table fields. User has the option to make modification in the records by using the filter (e.g. WHERE clause). Apart from this, they can also link one table to many other tables and queries.
Why MS Access Update Query is Not Working?
Behind this update query not working issue there can be several reasons, let’s check out each of them.
If your MS Access update query fails to change the table’s data, then the very first thing you need to check is; Whether the underlying Access table is updatable or not.
To check this, you just need to open your Access table and try to edit the fields manually. If you fail to do so, then it means the query also cannot make modifications too.
Well, there can be several reasons for this also:
The very first reason is that your Access database file is made “ReadOnly”. That’s why none of your data is been modifying into it.
If you are working with Access workgroup security then maybe you don’t the permission to edit any data.
For fixing this issue, just log in as administrator or with such user name & password which allows you with some suitable rights.
Your linked Access tables maybe not having such rights to make modifications on the data that is present at the backend data source.
Those who are linked to SQL Server table or working in the ADP (Access Database Project) but without having any primary key in the table. In that case, also you can’t edit any of your Access table’s field.
Generally, it is found that when the user can edit their table but unfortunately their query doesn’t work. Then, in that case, the user may encounter the following error message:
Operation must use an updatable query (Error 3073)
How To Fix Access Update Query Not Working Issue?
In this section, we will talk about some best fixes to resolve Access Update Query Not Working issue. Besides that, I have also outlined some reasons behind the inability to modify data using MS Access queries along with their quick fixes.
Solution 1 : Enable Read/ Write Permission In Web Application Folder
For dynamic websites, an Access database is also used as a data storage. Sometimes, user encounters, update queries not working or Operation must use an updatable query like unhandled exception error meanwhile the execution of the current web request.
Actually such an unhandled exception occurs when your Web application doesn’t have the write permission in the folder where your Access database is situated.
For updating the records, the user needs to make changes in the write privileges to the .mdb/.accdb file on the disk.
If in case the web application fails to write to the disk. Then the user can’t do an;y changes in the existing record neither they can add any new records.
So for this, the first solution is to enable the read/write permission in the database folder.
The second solution is to move the database to some other folder where the application will get the write permission.
Solution 2: Remove Read-only Attribute From Access Database Files
If your access database accdb/mdb file is read-only then it’s impossible to perform a write operation on it.
To enable addition or updating of records in the database again you have to firstly remove the ReadOnly attribute.
- You can perform this task in your Windows Explorer.
- Select your mdb or accdb file which is showing update query not working issue.
- After then make a right-click over it and choose the properties option.
- In the properties window, uncheck the ReadOnly checkbox.
Solution 3: Cut Off All The Exclusive Connection Of Your Database:
If your Access database connection is opened then disconnect it immediately. The reason is, if your database remains opened exclusively you are not allowed to open any other connection neither you can change any records.
So, every time when you try to execute any new SQL queries. Don’t forget to check your code whether you are disconnected from the database or not.
If in case, due to any reason you can’t disconnect from your database then use the same connection for other purposes instead of creating any new connection.
Tips To Avoid Update Query Not Working Issue
Mostly it is found that we all stuck in a situation where we are completely unable to edit data using the Access queries.
Here I have listed some best tips to avoid Update Query Not Working Issue in your database.
- If your query is having a TRANSFORM clause. Then, in that case, use a Crosstab query sets with a read-only attribute.
- If your query is having a DISTINCT predicate. Then you can resolve the problem of query update by setting Unique Values within the Properties of a query to No.
- If your query includes a GROUP BY Clause. Then make sure that a Totals query is assigned with a read-only attribute.
- If your query is requesting for some VBA function. Then the chances are high that your database is not present in a trusted storage location and thus the code execution is completely disabled.
- If your query is using the SELECT clause with any of these functions Sum (), First (), Max (), Count (), etc. then the queries having the aggregate records must be in a read-only mode.
- If your query’s SELECT clause is having a subquery. Then to resolve update query not working issue you need to go the subquery and make Uncheck of its Show box. Or else you can use the domain aggregation functions.
If even after trying all the fixes still the problem of Access query not working won’t resolve then the chances are high that your Access query is got corrupted.
How To Fix MS Access Query Corruption?
In such a situation it is recommended that you must take the help of professional MS Access Repair And Recovery Tool through which you can easily repair and recover your Access database from all types of corruption and errors.
With the help of this tool, you can just sit back and relax from the worry of losing your critical data. This tool can restore all data and database objects with all queries, table data and objects, primary key, table structure, and so on.
Hence, if you want to retrieve MS Access queries back then download this Access repair and recovery tool now.
Carefully follow all the mentioned reasons why the MS Access update query not working. As then only you can perfectly resolve your update query not working issue.
Don’t forget Update Query is an extremely powerful option in the Access database. A small mistake in designing query in Access may create more problems for you. So, avoid getting this happen….!