A key benefit to using databases is their ability to grow to meet your needs. However, the more changes that are made to the database, the greater the risk of it becoming corrupted or damaged. This can occur from any number of actions such as writing, reading or processing. As scary as database corruption can be, there are ways to prevent and fix these problems.
Included with Microsoft Access 2016 is the Compact and Repair Database command. We’re going to teach you how and when to use this tool for your own Access databases.
Why Corruption and Damage Occurs
As files grow larger, they have an increased risk of becoming damaged. This happens because the database receives new data and information. For example, Access sometimes creates hidden objects to accomplish certain tasks. These temporary objects might remain in your database even though Access no longer needs them.
Another issue that can happen is when deleting a database object. The disk space the object filled may not be reclaimed. While a few temporary and deleted objects may not be a big deal, an excess can cause problems over time. What you may see is that queries take longer to run or your database takes longer to load.
Decreased performance is never easy to deal with, but corruption is especially unsettling! No one wants to lose precious data. Data corruption is most likely to occur when a database is shared over a network and multiple users work within it. While you probably won’t lose your entire database over data corruption, it’s possible to lose some of it.
When to Use Compact and Repair
If you open a database that has been damaged or corrupted, you will be prompted to use the Compact and Repair command. We always recommend making a backup first.
- Compact on Close. The Compact on Close database option lets you automatically compact and repair a database when it closes.
- Manual run of Compact on Close. You can also manually run the Compact on Close option. This can be run on both open and non-open databases.
One word of caution: the compact and repair process can disrupt other users. If you plan on running this operation, let others know so they can avoid using the database at that time.
The Compact and Repair Database command is a great tool that comes with Access 2016. By understanding when and how to use this tool, you can address potential database problems early on – and save yourself a lot of headache.