Data corruption in MS Access is very common. (Part 1 of this article identified many possible causes.) In multi-user MS Access systems data corruption is even more common. For businesses that have come to rely heavily on the uninterrupted performance of their MS Access database, preventative practices and planning for a disaster is the best course of action. In addition, doing some forward thinking on the next stage of development for the business is prudent.
Best Practices for Prevention MS Access Corruption
- Train users to simply close the database when not in use
- Consider using a power conditioner or battery backup for each workstation
- Troubleshoot and replace bad components, computers, and network hardware
- Ensure the latest MS Office service packs and JET packs are installed
- Remove unnecessary software from each workstation
- Keep the computer operating environment at the ideal temperature and humidity
- Ensure the hard disk has plenty unused of storage space. Depending on your database size, this should minimally be 200 – 300 hundred megabytes of free space.
- Periodically run the Compact and Repair Database command
- There are two main reasons to run the Compact and Repair Database Command.
- First, as a database gets used the database file gets larger and becomes less efficient. Periodically running the Compact and Repair Database command will reclaim unused disk space that was held by deleted records or other temporary objects and optimize the use of the disk space so that performance does not continue to degrade.
- The second reason a user should run the Compact and Repair Database command is to repair the database due to corruption or if other problems are suspected. In the case of corruption, if the software detects a problem, MS Access will prompt the user to run the Compact and Repair Database command at start-up. Upon completion of the command, MS Access will provide feedback on the success of the operation. In the event, MS Access does not fully repair the database, contacting an expert that is familiar with repairing MS Access databases is your next step.
- Note: Before Running the Compact and Repair Database command:
- Always make a backup of the database
- Notify other users to log out of the database
- There are two main reasons to run the Compact and Repair Database Command.
- Consider splitting the database into front end and back end
- Splitting the database is a common practice that is built in to the MS Access software. Ultimately this process creates two linked .mdb files. The first file, the “back end”, contains all of the unseen details of the data in tables and their relationships as well as the data itself. The second file, the “front end”, includes all of the interface components that the user sees such as forms, queries, modules, macros and reports. By splitting the database each workstation will already have a copy of the front end on their system. Therefore, only the data is shared from a central network. This not only improves performance but, ultimately, significantly increases the security and reliability of the data.
Backup, Backup, Backup!!!
A backup copy of the full database is the fastest and safest way to recover from corruption or any other unintended changes to a database. Therefore a strict backup plan should be implemented and followed. How often a backup copy should be saved mostly depends on how often your database has major changes. Many businesses will perform a backup every 24 hours (at a time when there are no other users in the database). In general, it is more critical to perform regular backups when the database has design changes, large amounts of data changes, more than one user updating the data, or when an action query is performed to delete records or change data. In many cases, it makes sense to make a copy immediately prior to making any major design or large data changes.
Expert Tip: Be proactive!
The good news is… your database has served you very well. The bad news is…your database has become critical to your organization and you can’t live without it. Be proactive! The best time to be ready to move to a new database is before any disaster strikes. There are many choices for highly reliable and full featured databases from off-the-shelf to fully customized solutions. Each database option might manage e-commerce, online marketing applications, inventory, finances, record and image storage, online reporting security or any other feature in a more favorable manner than other options. The best fit for your business may not be obvious and the wrong choice can be costly. For these reasons, it is strongly advised to use a knowledgeable and experienced database expert to help with your selection.