Can't fix corrupted records

[Ron]

HI - I've run into a situation with an AccessXp database
where a few records have become corrupted and it is not
allowing me to repair the database.
I can't even get into the table to delete these bad
records. I can't even export the data to an Excel file
without Access shutting down.
I even tried to export the entire database piece by piece
into a new database, but when it reaches that table it
shuts down.
Is there any other way to repair this table? Like a
backdoor into it or some program that can go in and flush
those bad records out?
Please Please help!! I've run out of ideas and time with
this.
Thanks so much in advance!!

 

[Joseph Meehan]

HI - I've run into a situation with an AccessXp database
where a few records have become corrupted and it is not
allowing me to repair the database.
I can't even get into the table to delete these bad
records. I can't even export the data to an Excel file
without Access shutting down.
I even tried to export the entire database piece by piece
into a new database, but when it reaches that table it
shuts down.
Is there any other way to repair this table? Like a
backdoor into it or some program that can go in and flush
those bad records out?
Please Please help!! I've run out of ideas and time with
this.
Thanks so much in advance!!

Do you have a memo filed in that table?

Can you identify the corrupted records (that is can you see an error
message in that field is one or more records)?

 

[Guest]

You mentioned you tried to export the data, have you tried importing? Set up
a blank database and try importing the table into it. I have used this in
the past to clean up records. In some cases after it came in, I could
identify the record and delete it. In others, it simply did not import the
bad records.

Hope this helps.

 

[Ron]

Joe - There is a memo field and I am able to identify the
bad record. It won't let me delete it, but I'm trying to
copy all of the other 12,600 records into and Excel
spreadsheet to see if that will do the trick.
Do you know of any other solutions??

 

[Joseph Meehan]

Joe - There is a memo field and I am able to identify the
bad record. It won't let me delete it, but I'm trying to
copy all of the other 12,600 records into and Excel
spreadsheet to see if that will do the trick.
Do you know of any other solutions??

It has been a while since I have gone through this. It seems that memo
fields are more likely to become corrupted than text fields. When I found
out that, I eliminated from all my work. As I recall, and my memory has
never been very good. it is not the field that indicates it has a problem,
but rather the one before or after it. Those records need to be deleted. I
seem to remember that you may loose both records.

My experience was in a multi user LAN environment with a split system.
I had about 20 records out of a few hundred thousand that were bad on one
and only a couple on the other. Since it was critical data and, at that
time there was no backup, other than a copy that was also corrupted. I could
have been a real problem. Luckily those records were never needed. I also
switched to a multi layer back up system as well. No more problems after
getting rid of the memo fields, other than listening to users who want to
write books in a notes field.

 

[Birk Binnard]

Jackie L has the right approach. I've had this same occurrence several
times and fixed it by doing the following:

1. Copy the table format (only) to a new (empty) table
2. Open both the table with corrupted records and the new empty one
3. Select all the records in the bad table down to the first corrupt one,
but do not select the corrupt one.
4. Copy/paste these good records to the new table.
5. Repeat select/copy/paste until you have got all the good records copied.

It helps to know how many corrupt records you have, but you might not be
able to tell. When you are done, rename the corrupt table to something else
and rename the new table to the old corrupt table name. That shoudl do it.

The only problem with this method is that if your records have Autonumber
keys the keys in the new table will not be the same as they were in the old
one. Depending on how your application is structured this can range from no
problem to catastrophic.

Good luck.

 

[John Vinson]

Jackie L has the right approach. I've had this same occurrence several
times and fixed it by doing the following:

1. Copy the table format (only) to a new (empty) table
2. Open both the table with corrupted records and the new empty one
3. Select all the records in the bad table down to the first corrupt one,
but do not select the corrupt one.
4. Copy/paste these good records to the new table.
5. Repeat select/copy/paste until you have got all the good records copied.

It helps to know how many corrupt records you have, but you might not be
able to tell. When you are done, rename the corrupt table to something else
and rename the new table to the old corrupt table name. That shoudl do it.

The only problem with this method is that if your records have Autonumber
keys the keys in the new table will not be the same as they were in the old
one. Depending on how your application is structured this can range from no
problem to catastrophic.

You can actually run Append queries from the damaged table to the new
one; include the Autonumber field, and the existing values will be
copied faithfully to the new table. Just include criteria to select
only records known to be uncorrupted, preferably criteria on the
Primary Key.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Dave Hall]

Read Access Database Recovery Guide section of this page
http://www.fdrlab.com/tips.html

Dave Hall
FDRLab Data Recovery Centre Consultant
http://www.fdrlab.com/