How to fix possible db corruption?

Question

I'm at a client doing some quick fixes to their access application. It was a while I had a go with access, but I'm recovering quickly. However, I've discovered an interesting problem:

For some reports, I get a "Record is deleted" error. I've checked the reports, and it seems like there's a problem with one table. When opening that table, I find a record where all columns are marked "#deleted". So obviously, this row seems to be the culprit. However, when I try to delete that row, nothing really happens. If I re-open the table, the row still exists.

Is there a corruption in the db? How can I remove this record for good?

Edit: It's a MS2000-version

Solution: Simply compress/repair did not work. I converted the database to the 2003 file format instead, which did the trick. I've marked the first answer suggesting compress/repair, since it pointed me in the right direction. Thanks!

Answer 1

Have you tried the built in Access compact/repair tool? This should flush deleted records from the database.

The exact location varies according to the version of Access you're running, but on Access 2003 it's under Tools > Database Utilities > Compact and repair database. Some earlier versions of Access had two separate tools - one for compact, one for repair - but they were accessed from a similar location. If they are separate on the version the client has, you need to run both.

This should be a non-destructive operation, but it would be best to test this on a copy of the MDB file (apologies for stating the obvious).

Answer 2

Tony Toews, Access MVP, has a comprehensive guide to corruption:

Corrupt Microsoft Access MDBs FAQ

• Some corruption symptoms
• Determining the workstation which caused the corruption
• Corruption causes
• To retrieve your data

As an aside, decompile is very useful for sorting out odd happenings when coding and for improving start-up times.