A Corrupted MDB File: A Recovery Plan That Prioritizes Data Over Forms

Once an Access file is already damaged, the decision that matters is not how to repair it but what to save and in what order. The rows in the tables are the only part that exists nowhere else. Forms, reports, queries and modules can all be rebuilt on a schedule you can estimate. So the tables come out before anything else is attempted.

What is actually worth saving, and in what order?

Priority follows one question: if this object is lost, does a copy of it exist anywhere else in the world? Data has no such copy. A form does - it lives in the head of whoever uses it every day.

The practical consequence: if you have two hours with the file before it stops opening at all, all two hours go to rows 1 and 2.

The salvage order that does not lose more data

1. Get every user out and copy the damaged file to a separate folder. Everything from here happens on a copy; the original stays untouched as evidence.
2. Create a new empty database in the same Access version the original was authored in.
3. From the new file, use External Data to import objects out of the damaged one. Import reads from the source and never writes to it, which makes it the safest operation available.
4. Import tables only on the first pass. Do not tick forms and reports in the same run: one object that hangs can take down an entire import.
5. Import tables in small groups. When a group fails you know exactly which table is responsible, instead of receiving one blanket failure.
6. Only once the data is in should you try the queries, then the modules, then the forms and reports.
7. If a table refuses to come across whole, import it in slices by primary-key range. Some of the rows beats none of them.

Compact and Repair is a legitimate tool, but it rewrites the file in place, so it belongs after you have a copy rather than before. It also requires exclusive access to the file, meaning no other user is connected while it runs.

What MSysCompactErrors tells you

When Access repairs a file only partially, it records the objects it could not repair in a system table named MSysCompactErrors. That table is the most precise account you will get of what was lost.

• Empty means the repair was complete as far as Access is concerned - you still have to verify the contents.
• If it lists tables, that list is what you go and rescue from the oldest backup you can find.
• If it lists only forms and reports, exhale: those are rebuildable objects.
• Save a copy of it before you do anything else. A further operation can overwrite it.

Partial recovery: deciding what to give up

Partial recovery is the normal outcome, not the exception. The hard call is commercial rather than technical, and it belongs to whoever owns the process, not to whoever is running the import.

• Missing rows from the last day - usually reconstructable by hand from documents, email or whatever system fed them in.
• A missing lookup table (city list, product codes) - fast to rebuild, and occasionally rebuilt more correctly than it was.
• Missing historical rows - decide whether the history is operational or archival, and archive it separately if it is the latter.
• A missing relationship between tables - the data survives but the link is gone. This is the most expensive thing to reconstruct, which is why it earns high priority during the salvage itself.

How do you know the salvage worked?

A file that opens is not proof. Proof is numbers that agree with the world outside the database:

1. Compare row counts per table against the last clean backup, and write down the difference.
2. Run a control total on one key monetary column and compare it to a report produced before the failure.
3. Check the last row in every operational table: its date tells you the point in time the salvage actually reached.
4. Look for orphan rows - child records with no surviving parent. They are the signature of a relationship broken in transit.
5. Ask a user who knows the system to look up five real cases they remember. This catches what counts cannot.
6. Write the results down. Those differences are what you will be asked about in two months.

Limits worth knowing before you rebuild

If you are rebuilding, know what you are rebuilding into. The Access format is capped at 2GB per file, minus the space system objects need. Beyond that:

• Up to 32,768 objects in one database.
• Up to 255 fields in a single table.
• Up to 255 concurrent users.
• Up to 32 tables in a single query.

Those numbers are generous for a small business, and none of them is the real reason to move. The real reason is that a single file on a shared folder is one point of failure for the whole company. Moving the tables to SQL Server while Access stays as the interface removes exactly that point of failure without rewriting a single screen.

Related: the Access unrecognized database format message, a migration plan from Access to a web system, what a database is.

Sources

• Microsoft Support - Compact and repair an Access database
• Microsoft Support - Access specifications