ACCDB Crash Help
HomeACCDB Crash HelpThis page

How to repair files in MS Access using “Compact and Repair”

Compact and Repair Database in Microsoft Access

David Cimadevilla

Jul 19, 2019

How to properly use "Compact and Repair" in a corrupt Microsoft Access file, and what to do if you can't get rid of all the errors.

When an Access database is failing, Microsoft recommends applying Compact and Repair to fix all of the errors.

However, the repair is not always completed without complications, and you may be left faced with one of the following 3 problems:

  1. An error prevents you from even starting to make repairs.

  2. In the repaired database some objects are missing: tables, forms, Visual Basic, etc. They vanished completely.

  3. The repaired database still has errors.

I will talk about these throughout the article, but before I do so I will deal with a couple of points about how to use Compact and Repair Database.

Attention, dangerous tool

Compact and Repair can potentially destroy data in damaged files.

When you apply Compact and Repair on a corrupt file it can happen that, once the operation is complete, some objects, tables, or records might be deleted from the database if they were failing or producing errors. Unfortunately, without a backup, you will most likely have lost them.

It would be helpful if the tool displayed a warning before making destructive and irreversible changes to a database:

Microsoft Access Notice: This operation will rewrite the whole file. Inconsistent, erroneous or deleted data will be discarded from the file. Are you sure that you want to continue?

Fictitious warning before compacting a database.

But what really happens is that Compact and Repair proceeds silently with the regeneration of the file discarding everything that gets stuck, so silently that you could even doubt whether you have already clicked the compact button or not.

If your objects or tables have disappeared after compacting, it's possible that they are still there but not visible although they'll most likely be lost.

In short, always make a backup before using Compact and Repair Database.

Two useful notes

...about Compact and Repair.
  1. It can be started from two different places in the Access interface: from the Database Tools menu, and from the File | Information menu.

  2. It can be started in two different ways: with the database open and closed.

Repairing an open database

I always prefer to repair when the database is closed to avoid modifying the original file and thus preventing a possible loss of data. However, if you prefer to do it without closing the database, it is advisable to make a backup first.

Once started, Compact and Repair will be applied immediately to the current database with no hint once the operation is completed. You will just be editing the new repaired database.

You can start the tool from these two places:

  1. Menu Database Tools | Compact and Repair Database:

  2. Menu File | Information:

Repairing a closed database

Repairing the database while it is closed is, in my opinion, preferable. This way, Access preserves the original file, and the repaired database is saved in a new file.

Let's see how to do it:

  1. If you have opened Access from the Windows Start menu, you will see the Access initial screen. Press the Esc key to exit this screen and access the menus.

  2. On the other hand, if you already have a database open, close it from the File menu.

  3. Now you can start Compact and Repair Database from the Tools menu.

  4. Two dialog boxes open in succession. The first asks you for the database you want to repair.

  5. The second dialog asks you for the name of the file to which the new repaired database will be saved.

    If you get an error in this step, read the next section.

Error during Compact and Repair.

In some cases, when trying to repair the database, an error stops the process:

Microsoft Access error: Unrecognized database format

Error Unrecognized database format when using Compact and Repair.

This error means that the main structure of the database is damaged to such an extent that Access is unable to do anything with the file, not even opening it to be repaired.

You can read more about this particular error in the monograph about error Unrecognized database format, part of the How I repaired 28 corrupt databases project, in which I experiment with various corrupt MS Access files.

Errors in the repaired file

Based on tests done with different files, it seems that the scope of Compact and Repair is limited to only some types of failure.

Problem objects may end up in the "repaired" database without changes. Or some errors may be fixed, but then new ones appear. And there are also cases where some objects become inaccessible and Access silently ignores any attempt to open them.

As an example, I include here some of the real errors I have found in databases that were supposedly repaired after applying the Compact and Repair tool:

Microsoft Access error: '...' is not an index in this table

'...' is not an index in this table

Microsoft Access Error: Cannot open or run macro '...', because it is not valid

Cannot open or run macro '...', because it is not valid

Microsoft Access error: Microsoft Access cannot find the object. If '...' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.

Microsoft Access cannot find the object

Microsoft Access error: The module name '...' is misspelled or refers to a module that doesn't exist. If the invalid module name is in a macro, an Action Failed dialog box will display the macro name and the macro's arguments after you click OK. Open the Macro window, and enter the correct module name.

The module name '...' is misspelled or refers to a module that doesn't exist

Microsoft Access Error: The Visual Basic for Applications project in the database is corrupt

The Visual Basic for Applications project in the database is corrupt

Microsoft Access error: The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms and reports. You should backup your database before attempting to open the database and delete the VBA project.

The database cannot be opened because the VBA project contained in it cannot be read

How to get rid of the errors

If the database still has errors, one way to fix them, which gives good results, is importing all objects from a new database. You can try the procedure both before or after using Compact and Repair to see which one gives you the best results.

Microsoft Access Menu: External Data - New Data Source - From a Database - Access

Import all objects to clean a database with errors.

During the import, Access may fail to import some objects because they are damaged. The problem objects will be lost and will not be included in the final database. On the positive side, the resulting file will generally be in good condition.

When Compact and Repair + Import into new database does not work as expected, you can try a data recovery tool. The advantages are that it will be easier and you will recover more data even for the most damaged files.

Missing objects in the repaired file

During the process of compacting and repairing, Access can discard tables, forms, and other objects that are damaged or are in an inconsistent state. These objects will not be added to the repaired file.

Additionally, any object that is deleted or, for some reason, absent from the database's internal index, can also disappear. Damaged tables, records, forms, reports, macros and Visual Basic code can be lost during the process.

You can use a data recovery utility as a diagnostic tool by using the free preview to discover orphaned or deleted objects that would not be visible in any other way.

If you have already applied Compact and Repair, it will be impossible to recover the missing objects as the file is completely rewritten during the process, which discards any faulty or deleted object.

Compact on close

Microsoft Access Option: Access Options - Current Database - Compact on close

Compact on close option in Microsoft Access.

This per database configuration is disabled by default. When activated, the database is automatically compacted each time you close it.

I recommend keeping it disabled, as this is a major operation that should be supervised. And above all, doing it manually allows you to make a backup copy beforehand.

Additional Resources

You can find additional information in our blog ACCDB Crash Help, and also in the series of articles How I repaired 28 corrupt databases, where I analyze and recover 28 corrupted Access files with different error types.