18.4. Data Corruption
Data corruption the term strikes fear into the heart of the hardiest Access guru. Hopefully, the people who use your database will be well behaved, the network it sits on will remain reliable, and your database will never be in danger. But just in case life isn't as kind to you, it's important to be prepared.
Data corruption is a catch-all term that describes what happens when part of a database file is damaged. Imagine Jessica Baxter is in the middle of applying a large update when a power failure hits (or an office prankster pulls out her network cable). The back-end database will be left in an invalid state, because only part of Jessica's information will have been successfully received. As a result, the record she was working with may be scrambled beyond recognition. And if you're particularly unlucky, the problem can affect more than one record or even make the whole database act a little odd.
18.4.1. Diagnosing (and Fixing) Corrupt Databases
Every Access expert should have basic data corruption survival skills. First, you need to be able to spot when a database has gone bad. Here are some telltale signs:
Once you identify that a database is corrupted, it's time to nurse it back to health. The first resort is always the compact and repair feature, which cleans up a host of problems and shrinks large, bloated databases back to more reasonable sizes. To try out this feature, open your database in Exclusive mode (Section 18.3.4), and then choose Office button Manage Compact and Repair Database. The process could take some time, particularly with a large database.
The compact and repair feature fixes only tables, not forms or reports . However, if you've been sensible and have created a split database, the back end won't have any of these types of objects anyway.
Note: Before you try to fix a corrupt database, make an immediate backup. That way you can try several repair strategies.
Sometimes, the compact and repair feature won't solve the problem, or it may just partly rehabilitate your database file. At this point, it's time to take over with other repair techniques. If the remaining problems are relatively minor (like a few rows with suspicious data), you may be able to simply delete the offending information and recreate it. But sometimes Access refuses to show corrupted records without bombarding you with error messages. If this is the case, select all the good records and copy them to another table. Then, delete the table with the corrupt data and rename your copy to take its place.
As a final resort, you can create a new blank database, and try to import the tables from the back end, using the importing technique described in Section 18.2.4. This forces Access to recreate each object and rebuild every index. Even if this doesn't work completely, you may find that you can import most of the tables.
As a last resort, you'll need to revert back to the last backup. You do keep backups , right?
18.4.2. Preventing Corruption
As scary as data corruption is, following a few guidelines can ensure it remains a rare occurrence:
Tip: Make sure you keep a collection of the most recent backups. If you keep only a single backup file, you run the risk that you may back up a database that's already corrupted, and you won't have an older copy to fall back on.