How to Compact and Repair an Access Database
Before you begin, ensure that you have a current database backup. Compact and repair is a very intrusive database operation and has the potential to cause database failure. The backup will be instrumental if this occurs. Remember that compact and repair creates a new database file. Therefore, any NTFS file permissions you applied to the original database will not apply to the compacted database. It’s best to use user-level security instead of NTFS permissions for this reason. It’s not a bad idea to schedule both backups and compact/repair operations to occur on a regular basis. This is an excellent activity to schedule into your database administration maintenance plans.
Why Compact and Repair Access Databases?
Periodically compacting and repairing Access databases is necessary for two reasons. First, Access database files grow in size over time. Some of this growth may be due to new data added to the database, but another growth is from temporary objects created by the database and unused space from deleted objects. Compacting the database reclaims this space. Second, database files may become corrupted, particularly those files that are accessed by multiple users over a shared network connection. Repairing the database corrects database corruption issues allowing continued use while preserving the integrity of the database.
