Access Corruption Avoidance Tip #2: Avoid Dangerous Data

In the second of our articles on avoiding Access database corruption, we explore some data types that are more subject to corruption. The best choice is simply to avoid them. However that's like saying, "The best way to avoid an auto accident is not to drive your car." Access database courses will show you how to use these data types, but we will show you how to use them safely.

Memo Fields

Memo files can indirectly cause corruption of the entire database. Even if you can repair the damage, contents of the memo fields are often lost. If you had important data stored there, it is gone. What do you do if you need to store large amounts of text in a record?

One alternative to memo fields is to store the text as an OLE object in Word format. This substantially reduces the chance of data corruption and is a more efficient method of storing a lot of text. Using OLE objects is a technique from advanced Access database courses.

If you still need memo fields, don't put them in the main table. Store them in a separate table with a one-to-one relationship with the table using them. This way if the memo table gets corrupted, your other data is safe. Add more protection by splitting the memo table into a separate file stored on your back end.

Picture Files

Access database courses demonstrate how to include graphics such as JPEGs or GIFs in your applications. This might be a product picture in a catalog, an employee photograph, or document scans. While there are many applications where graphics are useful, those files are another big source of corruption. Again the ideal choice is to avoid them but if you need to use them, protect the rest of your database.

The solutions for using graphics in your Access database are similar to those for memo fields. Store your graphics is a separate table with a one-to-one relationship with the main data table. Put the table in a separate file stored on your network back end server.

Access 2007 Attachments

If you took an Access database course that covered Access 2007 then you would have learned about a new feature: attachments. An attachment is a way of using an outside resource that is less clumsy and less storage intensive than using OLE objects. Unfortunately, they are just as subject to corruption. How do you deal with attachments? You guessed it - a separate table in a separate file.

If you use multiple types of data, they should each have a separate table and a separate file. For example if using both memo fields and pictures you would have a database file for each in addition to your main file. Remember that corruption can destroy the entire table so the more separation you have the more you contain the damage.