One corrupt record?

[Sheila]

We have been using an Access97 database for about 5 yrs
with little or no problems. Yesterday, when a user tried
to view one particular record, the whole program shut down
with "This program has performed an illegal operation."
Further investigation in datasheet view reveals that you
can view and navigate through that one record up to a
certain field. It is obviously corrupted, but how do I
recover it? Apparently the problem has existed long
enough that it manifests in all available backups.

Thanks,
Sheila

 

[Dirk Goldgar]

We have been using an Access97 database for about 5 yrs
with little or no problems. Yesterday, when a user tried
to view one particular record, the whole program shut down
with "This program has performed an illegal operation."
Further investigation in datasheet view reveals that you
can view and navigate through that one record up to a
certain field. It is obviously corrupted, but how do I
recover it? Apparently the problem has existed long
enough that it manifests in all available backups.

This is what I would do:

1. Note the primary key value of the bad record.

2. Create a new blank database and import all the objects from the
original except the corrupt table.

3. Use the DoCmd.TransferDatabase method to import just the table
structure -- not the data -- of the corrupt table into the new database.

4. Create a linked table in the new database, linked to the corrupt one
in the original db.

5. Run an append query to copy records from the linked table to the new
one, including all records except the one that is known to be bad. If
this succeeds, you're home free. If not, you could try copying and
pasting known good records.

6. When you've got all the good records, compact the new database, swap
it for the bad one, and you're off.

 

[Sheila]

There are 70 fields per record in this table and I've
narrowed it down to 2 fields. One is a 100 character text
field, the other is memo type. In datasheet view, all is
well until either of those fields in that record only
appear on the screen.