Troubleshooting Errors That Occur While Updating Data In Ms Access Queries

By Author: Company Article
Total Articles: 60
Comment this article

Errors do occur while updating data in MS Access queries. Here in this article, we would be discussing errors that usually occur in MS Access 2000 when you update data in queries. Various reasons for such errors are discussed here and their troubleshooting too. Actually what happens is when you try to update data in a query, you might receive one of the following error messages:
Operation must use an updatable query.
Or
This Recordset is not updateable.
These errors can land you up in catastrophic situations (in form of corrupt Access file) unless you repair Access file with productive Access recovery software. Firstly try hand on manually repairing or just avoiding the situation. Consider the following points to troubleshoot this issue. Some causes and precautions are also discussed in these points.
When the query is based on three or more tables and there is a many-to-one-to-many relationship, you cannot update the data directly in the query.
When the query is a crosstab query, Microsoft SQL pass-through query, Union query you cannot update the data in the query.
When the query is ...
... calculating a sum, an average, a count, or other type of total on the values in a field, you cannot update data in the query.
When the Unique Values property of the query is set to Yes, you cannot update data in the query. Set it to ‘No'.
When the query includes a linked ODBC table with no unique index or a Paradox table without a primary key, you cannot update data in the query. Add a primary key or a unique index to the linked table to update query.
If there is no permission for Update Data for the query or the underlying table, you cannot update data. To resolve this problem, assign permissions to update the data.
When the field that you try to update is read-only, the database is open as read-only, or the database is located on a read-only drive, you cannot update data in the query.
If there is locked record, updating cannot be performed.
When the query is based on tables with a one-to-many relationship, you must take care of following list
Enable cascading updates between the two tables.
Add the join field from the many side to your query so you can add new records.
Save the record. You can make changes to the many side join field.
Enter values in fields from the table on the many side.
Select all primary key fields of the ODBC tables to allow inserts to them
If above points are taken into consideration then you might avert the chances of error and hence corruption while updating queries in MS Access database. If anyhow corruption occurs in Access file, use professional tool to repair Access file in prudent way.
http://www.repairaccessfile.net/
http://www.nucleusdatarecovery.org/

Author of the article has expertise in the field of data recovery software, email recovery, email migration, hard drive recovery and password recovery. The entire range of his articles is information-rich and useful for dealing with data recovery issues and finding suitable solution. See More........ repair access file