How to fix possible db corruption?

Question

Hi!

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

you can also try this Command line utility

//andy

Answer 3

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.

Answer 4

Aside from the options already posted above, I've used another simple method aswell: Simply create a new MDB file and import all objects from the corrupted one. Don't forget to get system and/or hidden objects when you go this way.

Answer 5

Compacting and importing won't fix the problem for the error reported, which is clearly a corrupted pointer for a memo field. The only thing you can do is delete and recreate the record that is causing the problem. And you need to find ways to edit memo data (or eliminate memo fields -- do you really need more than 255 characters or not?) that does not expose you to corruption risk. That means avoiding bound controls on forms for memo fields.

Instead, use an unbound textbox, and in the form's OnCurrent event, assign the current data from the form's underlying recordsource:

  Me!txtMyMemo = Me!MyMemo

To save edits to the unbound control, use the control's AfterUpdate event:

  Me!MyMemo = Me!txtMyMemo
  Me.Dirty = False        ' save the whole record

Why are memo fields subject to corruption? Because they aren't stored in the same data page as the non-memo fields, but instead, all that is in the record's main data page is a pointer to some other data page (or set of data pages if it's a large chunk of data) where the actual memo data is stored. If it weren't done this way, a record with a memo in it would very quickly exceed the maximum record length.

The pointer is relatively easily corrupted, most often by a fatal problem during editing in a bound control. Editing with an unbound control does not eliminate the problem entirely, but means that the time in which you're exposed to danger is very, very short (i.e., the time it takes for those two lines of code to execute in the AfterUpdate event).

--
David W. Fenton
David Fenton Associates