Access database corruption reasons

Access database corruption can happen for a variety of reasons and can have a wide range of impacts: from simple inconvenience to catastrophic data loss.  It’s helpful to understand what can cause corruption in MS Access database files, and best practices to avoid it.

Potential causes:

1. Connecting to the database over a VPN.
2. Running a database that is not properly split into front and back end.
3. Hardware issues.  

Connecting to database over VPN:

Remote access to applications and data is expected these days.  As a desktop application, Access isn’t designed to be accessed remotely like a native app or web app.

As a result, users more frequently connect to MS Access applications remotely via a corporate VPN (Virtual Private Network).  There are safer ways to do this, and there are ways that can lead to data corruption. For example:

Safer approach: 

The recommended approach to connecting to a MS Access database over a VPN is to:

1. first connect to the VPN
2. and then connect to a remote workstation using a remote desktop software application, such as Microsoft’s Remote Desktop, LogmeIn or Splashtop.

Not so safe approach:

If once you connect to your company VPN you simply open the MS Access application on your laptop or  workstation, you may be risking data corruption.  That’s because a VPN uses a lot of the connection bandwidth for security, leaving little throughput for actual data.  This can lead to slower data transfer rates which yield poor performance. 

In addition, VPNs can occasionally experience data flow disruptions due to packet loss or dropped connections. These issues can lead to problems in data transfer during critical database operations such as inserts or updates, which can lead to data corruption.  If this is how you connect to your MS Access application remotely, think about switching to the Safer Approach if possible.

Running a Database that isn’t properly split into front and back end. 

Microsoft and Access Developers recommend that an MS Access Application that is accessed by multiple users and workstations on a network be split into a front end and back end file. 

The back end file should store data only (Tables).

The front end should contain all of the user interface and business logic objects, such as: Queries, Forms, Reports, Macros and Modules. 

Not only does this configuration separate the data from the objects that search for and display it, but it also helps reduce corruption or data loss concerns.

Hardware issues:

While less common than the examples above, hardware issues can also lead to Access database corruption.

1. Hard Drive: Just like any file, MS Access mdb and accdb files reside on hard drives.  If the drive is malfunctioning, the read and write operations can fail.  That can lead to corruption or data loss.
2. CPU: All applications and data running on a computer must be processed by the CPU.  While extremely rare, a bad processor can cause corruption in MS Access files as well as any other file types. NOTE: In over 30 years of experience working with MS Access databases, I’ve only seen this once.
3. RAM: Since a computer is frequently transferring data between its hard drive, RAM memory and CPU,  a machine with  RAM issues, can lead to data corruption in the files that computer is opening and writing to.

How to repair a corrupt MS access database

When data corruption occurs in your Microsoft Access files and impacts your business operations, your immediate desire is to remove the corruption.  While not always 100% effective, there are tools or steps you can take to address your issue.

Repair and Compact Tool

Built into MS Access is perhaps the best tool for fixing MS Access corruption: the Repair and Compact tool.  If you’re experiencing issues with your database, or getting warnings of corrupt files:

1. you’ll first want to ensure that you have a recent backup of the file,
2. and then use the Repair and Compact Tool to fix the issue.

This will address the majority of corruption issues. 

NOTE: This tool can and should be used periodically to:

1. address issues that may not be apparent and
2. shrink the data file to help with performance.

Third Party Tools

If the built-in repair and compact tool is unable to resolve your corruption problems, there are some third-party tools available that may help. 

WARNING: these tools are not designed by Microsoft. They’re third parties attempting to read the MS Access file format and extract as much “clean” data from the files as possible.  Sometimes this results in incomplete data, such as:

1. only a subset of all of your data tables are salvaged.  The rest are missing or remain inaccessible.
2. only some data in a table(s) is repaired. The rest is missing or still corrupt.
3. Other properties of a table schema are missing such as indexes, primary keys or foreign key relationships.

After the tool is finished, you’ll want to thoroughly review the results to identify if everything is there, or if you need to tweak the results.

Allegedly repaired, but not really

We’ve seen situations where an Access file seems to have been successfully repaired, only to find out that a handful of records in a table remain corrupt.  In this situation, the file seems fine. It can be opened, and most of the data can be accessed and worked with normally, however some records cannot be accessed or cannot be updated successfully.

If this happens, you’re left with a few options:

Create a new table with the same structure and use an append query to insert all records except the corrupt ones.  Delete the old table, rename the new table to the correct name.  If possible, recreate the records in the newly created table.

Leave it as-is.  Sometimes these records are not critical.

Delete the records.  Again, sometimes the records may not be critical.

Delete the records and recreate them.  If you’re able to delete the corrupted records and have the information to recreate them, this is a good choice.

Create (and test) regular backups:

As with almost any data in the IT world, it is best practice to keep reliable backups.  Sometimes a backup is your only option, so be sure to create them frequently and periodically test to confirm the backup can be restored successfully.

Address underlying issue(s): 

If you’re experiencing corruption and:

1. are accessing your database directly over a VPN,
2. do not have your Access application properly split into a front and back end
3. or are dealing with hardware issues

you’ll want to correct these issues as soon as possible to avoid continued data corruption or eventual data loss.

Front End Corruption vs Back End Corruption

Microsoft and Microsoft experts recommend splitting your MS Access applications into front end and backend files. 

When you do this, there will be multiple files that comprise your application. Any of them can be susceptible to corruption.  Handling corruption in a front end file is different from handling it in a backend file.  If you’re experiencing corruption in your:

Backend:

This is your data. It can be changing constantly.  Your first step should be to try to repair the backend data file.

Front End:

This is the user interface and business logic objects of your application.  These are not changed frequently, and a corrupt version can be replaced with a backup with no data loss. 

When repairing isn’t enough

If you’re running into a corrupt MS Access database or application, you will most certainly want to take steps to remove the corruption, whether that involves:

1. using a tool to fix the corruption, or
2. restoring from a recent backup. 

However, sometimes repairing is not a long-term solution. You may want to consider alternatives. Some factors in determining when repairing is enough vs not enough:

1. Frequency of corruption: If you’re experiencing frequent corruption (multiple per year or any amount that is disruptive to business) and you’ve ruled out the suggestions above, you may want to think about finding an alternative solution for your data and application.
2. Frequency of data changes: If the data in your database is frequently changing (addition, editing or deletion of multiple records per day by multiple users) and you’re experiencing corruption, it may be time to consider alternative database solutions.  On the contrary, if your data is fairly static, and the database is only used to look up information, recent backups may be sufficient so long as you’re not experiencing unsatisfactory business disruptions.
3. Criticality of the data or application:  If you’ve experienced multiple data corruption events involving mission-critical data or applications, you want to explore alternatives.

Migrating away from Microsoft Access

If you’re interested in migrating away from MS Access, some considerations you’ll want to take into account:

1. the size of the database: The size of your database and its expected growth are important factors in deciding what database system to migrate to.  You’ll want to select a system that can handle your database size and expected growth.
2. The complexity of the application, its business logic, security needs, etc., all factor into both the selection of the database system and the front-end platform.
3. Remote access: If you have remote users, you want to be sure you migrate to a system that allows easy yet secure access for your remote workforce or customers.
4. Relational vs NoSQL database model: Most business applications employ Relational Database Models where the data is highly structured and data between tables is related to each other (e.g. Invoice headers and Invoice Items).  MS Access is a Relational Database system as is MS SQL, MySQL and PostGreSQL.  NoSQL systems (non-Relational) such as MongoDB store unstructured data that is not related to each other.  Both are powerful tools when used properly, so it’s important to identify which is best for your application.

If you’re interested in learning more about the migration process, particularly migration to MS SQL, here is an article that dives into the options and process in more detail: How to Migrate an MS Access Database to an SQL Server Database.

If you’d like us to convert your Access application for you, check out our Access to MS SQL-based web app conversion service and reach out for a chat. 

Ready to move off of Access? Check out these Access Conversion Case Studies