Mark I said:
I have an Access 2002 database that has a line of corrupt
data in it (stuff like #error and chinese looking
symbols). The database works fine until someone goes past
that bit of information. I then have to restore the
database from a backup. I have attempted to go in and
remove this information but it just corrupts the database
again, without removing the corrupt data. I have tried
the compact/repair tool built into access, but I just get
an illegal operation a minute after it starts.
Does anyone know how to remove this corrupt data?
Peter Miller of pksolutions.com posted this a couple of years ago:
From: "Peter Miller" <
[email protected]>
Subject: Re: Is there a way to repair data in a corrupt table?
Date: Sunday, December 16, 2001 2:13 PM
[snip]
When a file corrupts, but still allows some access to the database and
its data, people often ask "how do I remove the corruption?". The
answer, most often, is that you can't, but luckily, you don't need to.
Instead of trying to extract, delete, purge, remove (etc) the
corruption, the better approach is to extract the non-corrupted data.
You mention that you have one corrupted table, and that the table is
only partially corrupted. Good. Extract all the non-corrupted tables
to a new file (you've done that, no doubt). Then extract the table
definition for the corrupted table (if it was the table definition
that was corrupted rather than the table data, you would not have been
able to open the table and see its data at all). Now create a link to
the corrupted table from the new database. All of this you may have
already done, but here's where you missed something. When you extract
the data from the corrupted table, only bring over the clearly
non-corrupted records. Whether you copy and paste or use other means,
do not include corrupted records in the range you are moving. If you
include corrupted records you probably (but non always) will not be
able to complete the data port. If you have many corrupted records,
you will have many data ranges (of good records) to specify and move,
but taking this approach almost always works in such cases.
If all else fails, we could recover the file for you, but my feeling
is that you're almost there. Just stay away from the corrupted
records when you move the good records out. Remember that sometimes,
even displaying a corrupted record will crash Access, so be careful
what you are displaying as you peruse the damaged table.
Finally, its important to note that often, in a situation like yours,
the damaged table will only show a subset of the actual records it
contains. For instance, a 10,000 record table may show a recordcount
of 2,000 records, of which 125 or so may be corrupted. If you use the
approach above, you would extract 1,875 records (ie, all those visible
within the corrupted table), but we would be able to recover at least
9,875 and probably all of the 10,000 records. If you have a good idea
of the expected recordcount for the corrupted table, you'll know
better whether any such orphaned records exist.
[snip]
