Section 18.4. Data Corruption | Access 2007[c] The Missing Manual

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:

Cryptic error messages that appear for no good reason, like "out of memory." (Keep in mind that you shouldn't confuse this with the always-common category of cryptic error messages that appear for a legitimate reason, like the "file already in use" error described in Section 18.2.4.)
Rows that contain gibberish, like ### or ??? . Often, you'll find these values in the last few rows of a corrupted database, which indicates that the rest of the data is probably kosherit's just the new additions that ran into trouble.
A complete inability to use the database . If you get the dreaded "unrecognizable database format" error, you know disaster has struck.

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:

Stick to the sensible defaults described in Section 18.1.1. If dozens of people try to make changes at once, you multiply the chance of a problem.
Always split your database (Section 18.2) to lighten the load on your back end and to keep forms and reports out of harm's way.
Use a reliable network. If your network connection isn't dependable, an update can get interrupted , which is a prime cause of data corruption.
Teach users to close the database when they're finished using itor even when they're taking a lunch break.
Use the compact and repair feature on your back-end database regularly (choose Manage Compact and Repair Database from the Office menu). As more and more people make changes, database files grow larger and more disorganized. The compact and repair command rearranges your database to be more efficient, smaller, and less likely to run into trouble.
Make backups as often as possible. Depending on how quickly you make changes, a daily backup may be sufficient. But there's no reason you can't make a backup every hour or even more often if needed.

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.