RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Recovering Data from a Corrupt MS Access Table

A corrupt table in MS Access means lost time and data. It can lead to a loss of revenue or even employment. Learn how you might be able to recover most of the data when the worst happens.

f you use MS Access to store and manipulate data, you know that corruption happens. While it may not be an absolute certainly, Access is more susceptible to corruption than other databases. Because corruption in Access is usually the result of improper use, you can largely prevent it by simply avoiding the improper use practices. When corruption does occur though, the cause will be irrelevant—you'll just want to recover as much data as possible.

Corruption comes in many forms, so it stands to reason that there are a number of ways to recover data. You could spend a lot of time trying one technique after another until something finally works, but pinpointing the actual type of corruption first will make the recovery more efficient. If the corruption is bad data, for example, you can usually recover almost all of the data. You will almost always have to re-enter the bad data, however. This article explains how to perform this data recovery when the worst happens.

Note that trying to recover data can actually compound your corruption problem. Before trying to recover the data, make a copy of the corrupted database. You read that right. That way, if something goes wrong, you're no worse off than before you started. In addition, don't try to recover data over a network; copy the .mdb file to a local system and work from there. Make sure you don't backup the corrupted database to your latest working (and hopefully, still uncorrupted) backup, because doing so will corrupt the backup as well.

Start with the Easiest Fix

If you suspect you're dealing with corruption, the best place to start is also the easiest. Access offers a built-in feature for repairing the database. So, cross your fingers and use this feature before doing anything else. If you're lucky, it'll work.

Figure 1. Compact and Repair Database: Run Access's built-in repair feature to resolve corruption problems.

In pre-2007 versions of Access, you'll find the Compact and Repair Database option on the Tools menu (usually a submenu deep, as shown in Figure 1). Use these version-specific instructions to initiate the repair process.

  • Access 2003
    1. Choose Database Utilities from the Tools menu.
    2. Select Compact and Repair Database.
  • Access 2007
    1. Click the Office button.
    2. Select Manage in the left pane.
    3. Choose Compact and Repair database.
  • Access 2010
    1. Click the Office tab.
    2. Click Compact and Repair in the right panel.

In all versions, Access will close the database, clean things up a bit, and then reopen the database.

Bear in mind that this feature resolves specific Access-related issues. In 15 years of working with Access, I've yet to see it repair a corrupt table. That doesn't mean it won't work for you though, so definitely try it first.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date