Rig for Sale
Apr 3, 2002 | Luke
Mason | E-Mail
Access corruption is not only notorious among developers and IT managers, but it
is also one of the most annoying computing disaster areas you’re likely to
encounter. The first two articles in this series focused on the causes of Access
database corruption and prevention strategies you can use. But if you haven’t
taken steps to prevent Access corruption, here are steps you can take to repair
corruption after it occurs.
How do you know an Access database is corrupted?
The typical symptoms of Access database corruption can easily be confused with
unrelated problems. When you start seeing strange VBA error messages, then it
could be a problem with your code. Access is most often used in smaller
organizations where testing budgets are limited, either in terms of time or
money. Testing a multiuser application is a lot more complex than running the
software on one machine and making sure that it works there. You may get
“lucky” and receive an “unrecognizable database format” message, which
will immediately let you know that the database has failed.
Catch up with the Access database series
The cryptic error message
More likely, however, you’ll start getting error messages that make no sense.
“Out of memory” is not something that you would expect to see on a modern
office PC, so that message is suspect. Also look out for any error messages that
refer to invalid or missing bookmarks, or objects that cannot be referenced. If
you see Nulls throwing up error messages in places where the data doesn’t
contain any Null entries, then that is also a sign. The golden rule with these
weird errors is if the message sounds impossible for the current state of the
application, then the database is probably corrupted.
Warning: Database recovery risks
Below are the methods I recommend for recovering a corrupt database, in the
order in which I would suggest you try them. NOTE: Each of these methods
could cause more damage to your database, so don’t work on only one copy!
Make a primary backup and several copies and try each stage on a new copy. Label
each file with the time and date you last attempted to repair it so that you
don’t lose my “good” copy of the original, corrupted database. Also, copy
the database MDB file to your local hard disk before you start. Most of these
methods take awhile for the computer to complete but will be expedited if you
work off a local hard disk rather than over a network.
1. Try to “compact and repair” the database
This is the obvious first step and much of the time it will apparently repair
the file. I’d recommend that you don’t use the compact and repair utility
that is built into Access. Instead, use the JetComp utility that Microsoft
provides, which, according to MS support, has some improvements over the
built-in utility. You can find more information on JetComp, including where to
download it, from the Microsoft
Knowledge Base. A word of warning about this approach: The utility will only
repair tables. Forms and reports will not be repaired and if one of these has
gotten corrupted, you’ll have no choice than to rebuild it from scratch. You
should, however, be able to copy and paste any VBA code between the old damaged
form and your new one. Finally, this utility doesn’t always repair all of the
data in your tables, although it may do enough to allow you back into the
database. If you put the database straight back into production, you could find
that it will become corrupted again very quickly, and chances are, you won’t
be able to recover it a second time.
2. Try importing all data
Create a new, blank database and import all objects from the corrupted database.
This will force Access to re-create each object, including tables and indexes,
which seem to get corrupted more often than anything else.
3. Check the last few rows of each table
If you receive repeating error messages when opening a damaged table or when
importing its data into a new database, take a look at each row, starting at the
bottom. If you see rows containing “###” and other gibberish, then this is
probably the only point at which the database is corrupted—you may have lost a
row or two, but you can probably recover everything else. Most of the time,
Access won’t allow you to select and delete the row because it will bombard
you with error messages as soon as this line is even visible on the datasheet.
The trick is to move it just off the screen and select every other record. Copy
the data and paste it into a new table.
4. Look for corruption relationships
If you’re dealing with related tables, then you’ll probably have real
problems with primary and foreign keys. If you’re using AutoNumber fields to
generate a unique key for each row, then pasting the data will cause new keys to
be created, thus breaking every single relationship and rendering your data
useless. In this situation, there is very little you can do other than write an
append query to copy each row individually into a new table. Create a temporary
field in the new table to store the old key value in and then re-create
the relationships using these old values in an update query. You should then be
able to run an update on each row to match the new keys, while keeping the
integrity of the data by matching the old keys.
If you find that you have damaged data in certain rows and Access won’t let
you delete the rows, try exporting the table to a comma separated values (CSV)
file. Load this into Excel and look for any data that looks like garbage:
“###” and “???” are good starting points. If you have the time and
patience, then it is possible to clean up an Access table in Excel and then
import it back into the database.
5. Check that your primary keys are where you expect them
I once repaired a corrupted database with JetComp and found that a certain form
that had previously worked was now not allowing any data to be entered or
changed. The status bar read “This recordset is not updateable”—an error
that you would expect to see in certain types of recordsets with SQL joins
(relationships) in them. The form in question was drawing data from three
separate tables and joining them in a query that was the datasource
property of the form. The SQL in the query was identical to before corruption,
as was the data and the form. So what was causing this error message when the
form had worked without a hitch for months? After several hours of frantic head
scratching, I happened upon the cause: The primary key was no longer identified
as a primary key in the design of the table. This factor was breaking the
relationships in the SQL statement of the query, so the form was falling over as
soon as I tried to change the data. Marking the field as a primary key and
saving the table design fixed the problem.
Make backup copies
If you haven’t yet found the source of corruption, make frequent copies of
your database. A little VB program can copy the file over the network to another
computer every hour, or even every half hour. Telling your users that they’re
going to have to reenter all data since the last evening’s backup is a
surefire way to get lynched before you make it to the door. If you tell them
that they’ve lost the last hour’s work, they’re likely to be a lot more
Corruption is always possible to recover from assuming you take the necessary
precautions. Your data is far more valuable than your hardware and should be
your number one priority. If bits do start falling off your database, then
don’t panic; stay calm and start making copies. Chances are high that you’ll
be able to recover all the data within an hour or two, and then you can start
concentrating on preventing it from happening again!
Created Date: 04/25/2002 Last Reviewed: 04/25/2002 Rev. Date: