Access corruption: Database recovery

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 to be
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 forgiving.

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: