Fixing Corruption: 'AOIndex' is not an index in this table

There is a particular form of database corruption that has been seen in recent years. The symptom is that, when you attempt to open the database, you receive this error message:

'AOIndex' is not an index in this table.

Of course, the best protection against database corruption is to take frequent backups and avoid running an un-split database across the network, but it never hurts to try to recover from it where possible. This particular form of corruption is reparable in some cases. I've created a utility database that can be used to attempt to repair this corruption.

Technical Details

Inspection of the hidden system table "MSysAccessObjects" in a database that was showing this corruption revealed that there were a number of records in the table that had Nulls in both the ID and Data fields, which makes no sense; and further, there was no index on that table, which in a good database has an index named -- you guessed it -- "AOIndex". So one may attempt to repair this corruption by deleting the "bad" records from MSysAccessObjects and rebuilding the index using DAO. The code to do this must be run from a separate utility database, since one can't get into the corrupt database to run any code.

If you are interested in the code, and not just in fixing your corrupt database, download the utility database, "FixBadAOIndex.zip", open it while holding down the Shift key (to disable the Autoexec macro), and inspect the procedure "FixBadAOIndex" in module "basFixAOIndex".

IMPORTANT DISCLAIMER

This database is provided, without warranty or representation of any kind, in the hope that it may be helpful in fixing a specific form of Access database corruption. Any other use is at the sole discretion and the sole risk of the user. Neither DataGnostics LLC, nor Dirk Goldgar, makes any promise regarding the features and function of this software. It may not function as you expect, or may not function at all in your environment. Furthermore, there is always a possibility that it may cause irreparable damage to the subject database. Before running this application against your database, be sure to have a backup copy of that database.

If you do not agree to these terms, do not open and execute the code in the utility database.

Using the Utility To Fix the Corrupt DB

DO NOT USE THIS UTILITY TO FIX OTHER FORMS OF CORRUPTION!

If you have a database that is giving the AOIndex error, you can use the following steps to attempt to repair it:

1. Make a backup copy of the corrupt database, in case this attempt causes further corruption. Then make another copy, just in case.
2. Download the utility database and extract the .mdb file from the zip archive. If your version of Access requires that code-bearing databases be stored in a trusted location, be sure to extract the utility database into a trusted folder.
3. Double-click on the FixBadAOIndex.mdb file, or otherwise open it. If you get any message or prompt about the code in the database being disabled, be sure to enable the code.
4. You will be shown a file-browse dialog to select the corrupt database to be repair. Navigate to and double-click one of your copies of the corrupt database, or select it and click "Open".
5. The utility will ask you to confirm that you have a backup. If you answer No, the utility will quit without touching your database.
6. If you answer Yes, the utility will ask you to enter the database password. If the database is protected by a password, enter the password and click "OK". If not, click "Cancel".
7. The utility will now attempt to repair the corruption. The attempt will take almost no time at all.
8. The utility will tell you whether its repair attempt appeared to succeed or not. Please note: even if it appears to have succeeded, that doesn't necessarily mean your database's corruption has been fixed. There may be other corruption present in the database. The only proof is if you can now open the repaired database.
9. If the repair attempt succeeded and you can now open the database, I recommend that you create a new blank database and import all the objects from the repaired database into it; then use that new database henceforth. That should eliminate any hidden corruption that may remain.

I'd be interested in hearing if you used this utility, and what the outcome was. Please drop me a line to let me know.