'...' is not an index in this table
Cannot open or run macro '...', because it is not valid
How to repair files in MS Access using “Compact and Repair”
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:
An error prevents you from even starting to make repairs.
In the repaired database some objects are missing: tables, forms, Visual Basic, etc. They vanished completely.
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:
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.
It can be started from two different places in the Access interface: from the Database Tools menu, and from the File | Information menu.
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:
Menu Database Tools | Compact and Repair Database:
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:
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.
On the other hand, if you already have a database open, close it from the File menu.
Now you can start Compact and Repair Database from the Tools menu.
Two dialog boxes open in succession. The first asks you for the database you want to repair.
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:
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 cannot find the object
The module name '...' is misspelled or refers to a module that doesn't exist
The Visual Basic for Applications project in the database is corrupt
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.
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
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.
