Why you Can't Save an Access Database After Updating a Field

Last week we discussed how to modify the length of a field in an Access database. This process includes documenting the linked relationships, breaking the existing relationships where the field being changed exists, updating the fields in all tables where it exists and saving all modified tables. Unfortunately, when trying to save tables with updated fields, you may instead receive an error stating that the table cannot be saved.

This post addresses why the error happens when saving a modified table, as well as how to bypass this error so the tables with updated fields can be saved. Additionally, we demonstrate how to recreate the linked relationships that were broken in a previous step to be able to update the field in the first place.

Why you Can't Save an Access Database After Updating a Field

If, after modifying the field length, you receive an error stating there isn't enough disk space or memory, you will not be able to save the table. To bypass this error, which is not directly caused by a disk space or memory issue, you will need to make a change to the registry on the device where you are updating the field. With the correct registry change made, you will be able to save the table with the new field modification.

For more information about this error, you can read this article from Microsoft. In general, this issue happens mainly in very large databases because there is a registry setting set when Access is installed that limits how many records can be locked at one time. Large databases can require more than the default setting, which causes the save to fail.

Saving tables with the save error

NOTE: The steps involved in this process are advanced, and as such, should only be performed by someone who is comfortable working with the Windows registry. Making a mistake, or failing to follow the directions exactly as listed can have dire consequences. These may include, but are not limited to, rendering the device unstable or unable to boot the Windows operating system. Please proceed with caution and at your own risk.

• Locate the registry key that corresponds to your system and software using the Microsoft article listing several different locations in the local registry.
• Once you locate the appropriate registry key, increase the default MaxLocksPerFile number from 7500 to 25,000.
• Try saving the table with the changed field. If the maximum number of file locks is enough, the file will save without issue.
• If you still receive the disk space/memory error, increase the max file locks number in the registry until you are able to successfully save the table. Be sure to increment the number by at least 5,000 records each time you increase the number.

Once all the tables with the updated fields have been saved the last step is to recreate the linked relationships between tables.

Recreating linked relationships

Recreating the linked relationships is a series of steps that will be much easier since we documented all of the relationships we intended to break in a series of steps in the first post.

To recreate the original linked relationships between tables:

• Close any open tables.
• Open the Relationships tab.
• Open the image(s) of all original linked relationships saved in an earlier step.
• On the relationships tab in Access, left-click and drag the field listed in the left side under Table/Query of the Edit Relationships image taken for that table. Drag the field and drop it on top of the field listed in the table on the right side under Related Table/Query.
• This brings up the Edit Relationships box for those tables.
• Make any necessary changes to replicate the linked relationship and ensure the Edit Relationships box looks exactly like the original image.
• Click the "Create" button to create the linked relationship.

• Continue this process with every linked relationship that was broken until each linked relationship between tables has been successfully recreated.
• NOTE: Be careful to ensure that each relationship recreated exactly matches the original linked relationship.
• Open one of the tables with the modified field and verify it has the lengthened field and works as expected.

Access databases can be extremely beneficial and have many uses. If you use an Access database, you may run into a reason why you need to lengthen the size of a specific field that is linked to other tables. This causes Access to alert you that you will break those relationships if you proceed. Luckily, by following a series of steps you can modify an existing field, regardless of how many tables it is in.

Documenting the relationships between tables with the field that will be changed, breaking the linked relationships, changing the field in every table where it exists, saving those tables and recreating the linked relationships will accomplish this. If you run into a save error, the steps above can help you bypass this issue. Following the steps of this process should help prevent any issues with the database file, but it is always recommended to make a copy before making any big changes to a database file.

As always, a little bit of planning goes a long way when it comes to more complex applications and data!