Microsoft Access Database Corruption – Why it happens and when to repair vs replace

Insights and opinions from a Microsoft-stack focused software development firm.

Access database corruption can happen for a variety of reasons, and its impact ranges from a simple inconvenience to catastrophic data loss. Understanding what causes corruption in MS Access database files — and the best practices that prevent it — puts you in a far better position to protect your data and your business.

Access Database Corruption Reasons

The most common causes of MS Access corruption fall into three categories:

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

Let’s look at each in turn.

Connecting to the Database over a VPN

Remote access to applications and data is expected these days. But Access is a desktop application — it was never designed to be accessed remotely the way a native app or web app is.

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

The safer approach

The recommended way to connect to an MS Access database over a VPN is to:

1. First connect to the VPN.
2. Then connect to a remote workstation using remote desktop software, such as Microsoft’s Remote Desktop, LogMeIn, or Splashtop.

The not-so-safe approach

If you connect to your company VPN and then simply open the MS Access application on your laptop or workstation, you may be risking data corruption. A VPN consumes a large share of the connection’s bandwidth for security, leaving little throughput for actual data. The result is slower data transfer rates and poor performance.

VPNs can also experience data flow disruptions from packet loss or dropped connections. When those interruptions hit during critical database operations — inserts or updates, for example — the data transfer can fail mid-operation and corrupt the database. If this is how you connect to your MS Access application remotely, consider switching to the safer approach if at all possible.

Running a Database That Isn’t Properly Split into Front End and Back End

Microsoft and experienced Access developers recommend that any MS Access application used by multiple users and workstations on a network be split into a front end file and a back end file:

• The back end should store data only (Tables).
• The front end should contain all of the user interface and business logic objects: Queries, Forms, Reports, Macros, and Modules.

This configuration not only separates the data from the objects that search for and display it — it also meaningfully reduces the risk of corruption and data loss. If your multi-user Access application still runs as a single, unsplit file, splitting it should be high on your list.

Hardware Issues

While less common than the causes above, hardware problems can also corrupt an Access database:

1. Hard drive: Like any file, MS Access .mdb and .accdb files live on hard drives. If the drive is malfunctioning, read and write operations can fail, leading to corruption or data loss.
2. CPU: Everything a computer runs must pass through the CPU. While extremely rare, a bad processor can cause corruption in MS Access files — as well as in any other file type. Note: in over 30 years of working with MS Access databases, I’ve seen this only once.
3. RAM: A computer constantly moves data between its hard drive, RAM, and CPU. A machine with faulty RAM can corrupt the files that computer opens and writes to.

How to Repair a Corrupt MS Access Database

When corruption strikes your Microsoft Access files and disrupts business operations, your first instinct is to get rid of it — fast. While no method is 100% effective in every case, there are tools and steps that address most corruption issues.

Repair and Compact Tool

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

1. First make sure you have a recent backup of the file.
2. Then run the Repair and Compact tool to fix the issue.

This resolves the majority of corruption problems.

Note: the tool can — and should — be used periodically, even when nothing seems wrong. Regular runs address issues that may not yet be apparent and shrink the data file, which helps with performance.

Third-Party Tools

If the built-in Repair and Compact tool can’t resolve your corruption problems, there are third-party tools that may help.

Warning: these tools are not designed by Microsoft. They are built by third parties attempting to read the MS Access file format and extract as much “clean” data as possible. Sometimes the results are incomplete:

1. Only a subset of your data tables is salvaged; the rest are missing or remain inaccessible.
2. Only some of the data in a table is repaired; the rest is missing or still corrupt.
3. Parts of a table’s schema are lost, such as indexes, primary keys, or foreign key relationships.

After the tool finishes, review the results thoroughly to confirm everything is there — or to identify what needs to be fixed by hand.

Allegedly Repaired, but Not Really

We’ve seen situations where an Access file appears to have been repaired successfully, only for a handful of records in a table to remain corrupt. The file seems fine: it opens, and most of the data can be accessed and worked with normally — but certain records can’t be opened or can’t be updated.

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

• Rebuild the table. 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, and — if possible — recreate the lost records in the new table.
• Leave it as-is. Sometimes the affected records simply aren’t critical.
• Delete the records. Again, sometimes the records may not be critical.
• Delete the records and recreate them. If you can delete the corrupted records and you have the information needed to recreate them, this is a good choice.

Create (and Test) Regular Backups

As with almost any data in the IT world, keeping reliable backups is best practice. Sometimes a backup is your only option — so create them frequently, and periodically test that a backup can actually be restored successfully.

Address the Underlying Issue(s)

If you’re experiencing corruption and you:

1. access your database directly over a VPN,
2. don’t have your Access application properly split into a front end and back end, or
3. are dealing with hardware issues,

correct those root causes as soon as possible. Otherwise you’re inviting continued corruption — and, eventually, real data loss.

Front End Corruption vs Back End Corruption

Microsoft and Microsoft experts recommend splitting MS Access applications into front end and back end files. Once you do, your application consists of multiple files — and any of them can be susceptible to corruption. Handling corruption in a front end file is different from handling it in a back end file.

Back end

This is your data, and it can be changing constantly. Your first step should be to try to repair the back end data file.

Front end

This holds the user interface and business logic objects of your application. These change infrequently, so a corrupt front end can simply be replaced with a backup copy — with no data loss.

When Repairing Isn’t Enough

When you hit a corrupt MS Access database or application, you’ll certainly want to remove the corruption — whether by running a repair tool or restoring from a recent backup. But sometimes repairing is not a long-term solution, and it’s worth considering alternatives. Factors that help determine whether repairing is enough:

1. Frequency of corruption. If corruption is happening frequently (multiple times per year, or any amount that disrupts the business) and you’ve ruled out the causes above, it may be time to look for an alternative home for your data and application.
2. Frequency of data changes. If your data changes constantly — multiple users adding, editing, or deleting many records per day — and you’re experiencing corruption, consider alternative database solutions. Conversely, if your data is fairly static and the database is used mainly for lookups, recent backups may be sufficient, provided the disruptions stay tolerable.
3. Criticality of the data or application. If you’ve had multiple corruption events involving mission-critical data or applications, you should explore alternatives.

Migrating Away from Microsoft Access

If you’re considering a move off MS Access, take these factors into account:

1. Database size. The size of your database and its expected growth are important in choosing which database system to migrate to. Select a system that can handle both your current size and your projected growth.
2. Application complexity. The complexity of the application, its business logic, its security needs, and similar factors all influence both the choice of database system and the choice of front-end platform.
3. Remote access. If you have remote users, make sure you migrate to a system that gives your remote workforce or customers easy yet secure access.
4. Relational vs NoSQL. Most business applications use a relational database model, where data is highly structured and tables relate to one another (for example, invoice headers and invoice items). MS Access is a relational database system, as are MS SQL, MySQL, and PostgreSQL. NoSQL (non-relational) systems such as MongoDB store unstructured data without those relationships. Both are powerful tools when used properly, so it’s important to identify which is right for your application.

If you’d like to learn more about the migration process — particularly migration to MS SQL — our article “How to Migrate an MS Access Database to an SQL Server Database” walks through the options and process in detail. You can find it alongside our other guides in our articles.

And if you’d rather have Techlancer 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

Browse our case studies to see how businesses like yours have replaced aging Access applications with modern, reliable solutions. Related reading in our articles includes “How to Migrate an MS Access Database to an SQL Server Database” and “When to Rewrite your software”.