Summary: You may encounter errors when updating data in the Access database. Let’s understand the reasons behind the “Access database not updating” issue and the solutions to resolve this issue. You will also learn about an Access repair tool that can help fix the “database not updating” issue if it occurs due to corruption in database file.
Many users have reported experiencing the “database not updating” error while updating forms, tables, queries, or records in Access database (MDB/ACCDB) files. It usually occurs when you try to use “update queries” or records through a VBA code in the Access database. Such an error can also appear when you forget to use an updatable query in a code or if you are using a record set that is not updatable. When you try to update or delete data in the database via query, you can receive one of the below error messages:
- This record set is not updatable.
- Access linked table not updating.
- The operation must use an updatable query.
- Updating data in a linked table is not supported by this ISAM.
Causes of “Access Database not Updating” Issue
You may fail to modify the data in the Access database files due to many reasons. A few of them are cited below:
- You cannot update the query directly if it involves multiple tables or if there are many-to-one, many-to-many relationships.
- If you trying to update a crosstab query (such a query is used to present the large information in compact form).
- If the queries in an Access file are corrupted or damaged.
- If the table in which you are trying to use the update query is corrupted.
- When the query is a union query or its unique value property is set to Yes.
- If the query contains a linked ODBC table without a primary key.
- When you are not permitted to update data in a query.
- When there are multiple tables in a query and you forgot to join the tables.
- If you’re trying to update the calculated field.
- When you are trying to update a read-only database.
- When you are attempting to update the locked records.
Methods to Fix Access Database not Updating Issue
You can follow the below-mentioned methods to resolve the unable to update the database issue in MS Access.
Method 1: Enable “Cascading Updates Related Fields”
You can get the ‘unable to update query’ error if your query involves multiple types of table relationships. In such a case, you can use cascade update tool in Access. It has various options to ensure the validity of the data, such as “Cascade Update Related Fields”. You can try enabling Cascade Updates Related Fields option to fix the Access database not updating issue. To enable this option, follow these steps:
- Open the Access database.
- Go to Database Tools and then click Relationships.
- Double-click on the line between two tables.
- The Edit Relationships window is displayed.
- Select the checkbox named “Cascade Update Related Fields”.
- Click OK.
- Now close the Relationships window.
Method 2: Check the Record-Locks Property of the Forms
Sometimes, Access Database fails to update due to record-locks settings. This setting can prevent you from updating the same record at the same time. So, ensure that you set the record-locks property correctly. To check these settings, follow these steps:
- Go to the Microsoft Office button.
- Select Access Options.
- In the Access Options wizard, click on the Advanced button.
- Select on the option you need, under the Default record-locking section.
Method 3: Change the Properties of Database
You may get the “Access database not updating” issue if you are trying to update the data in a read-only database. In such a case, you can change the read-only attribute. Here are the steps:
- Open File Explorer on your system.
- Search the Microsoft Access database that you need to move.
- Right-click on the database and then choose Properties.
- In the Properties dialog box, under the Attributes section, unselect the Read-only checkbox.
- To apply the changes, click the OK button.
- Now check if you are able to update the database.
Method 4: Check the Allow Edits Property
You may encounter the “Access database not updating” issue while editing a form in the Access database. Access has an ‘AllowEdits property’ to specify whether you are allowed to edit the saved records in a form or not. You can check the property using the below steps:
- Open the Access database.
- Open the form (where you are getting the error) in the design mode.
- Right-click on the form and select Form Properties.
- In the Property Sheet dialog box, click Data and then change the option to Yes against Allow Edits.
- Save the changes and close the window.
Method 5: Check the Validation Rule in the Property Sheet
The Access database not updating error can occur if there are defined validation rules in the form. The validation rules are used for controls in the form and table. You can check for the validation rules in a form in the database using the below steps:
- Open the form in the design mode.
- Click on the Properties button.
- Click Data and then Validation Rule.
- Check if any Validation Rule has been set.
- Do the changes (if required) and then save the applied changes.
Method 6: Repair the Access Database
Sometimes, corruption in database file can prevent you from updating the data in it. In such a case, you can use the Compact and Repair tool in MS Access to repair corrupted Access database files. To use this tool, follow the steps given below:
- Open the desired Access database.
- Go to the File menu.
- Go to the Info tab and then click on Compact & Repair Database.
Microsoft Access will create a copy of the repaired database at the same location where your database is stored.
The Compact and Repair utility, sometimes, fails to work or is unable to fix the corruption issues. In such a case, you can opt for a third-party Access repair tool, such as Stellar Repair for Access. It is a powerful Access repair tool designed to repair severely corrupted Access files. It scans the damaged file and restores all the data from the corrupted database, such as object, form, macros, etc.
Closure
In this post, you have read what causes the “Access database not updating” issue and the possible solutions to fix this issue. You can check the record-lock settings or Allow Edit property. However, if the error occurs due to corruption in the database, then you can try an Access repair tool, such as Stellar Repair for Access. It is a highly efficient Access file repair tool that can repair Access database files. The tool is compatible with Windows 11/10/8/7/XP.
