Troubleshooting errors that occur while updating data in MS Access queries

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.