One corrupt record?

  • Thread starter Thread starter Sheila
  • Start date Start date
S

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
 
Sheila said:
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.
Click to expand...

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.
 
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.
 
There is a known problem with the memo fields and they are the devil to
find. I have some instructions, but I can't find them on my home machine.
I will try and find them at work Monday and if I find them I will post the
information.

After about three cases of the problem (Access 97 WinNT on a LAN) I
eliminated all memo fields, much to the consternation of some of my more
verbose users, but I also eliminated the crashes that they disliked even
more. I have not lost a database since then.
 
Here is some of the text, I will have to look some more for the file.
As I recall I never used the file.

It sounds like you have corruption in memo field. When you get a

message that you do not have read permission when compacting or if you get a
microsoft Jet database engine error (something about you another user are
trying to change the same record at the same time) - this indicates
corruption in a memo field.

I'va attached an application that I created to fix it. You will NOT get the
missing data back from the corrupt field (may only be one record) but the
database will be fully usable again. As always work with a copy of you data
first.

You can test for Microsoft error by opening the table in spreadsheet format
and Ctrl-F in the memo field and search for something you won't find - I
usually use zzzz
 
Back
Top