Microsoft Access Compact and Repair

Microsoft Access Compact and Repair is a tool built into Microsoft Access that can be used to reduce the file size of a database and also repair the database should it become unusable or corrupt.

Why Do I Have To Compact Repair My Access Database?

One of the answers to why you need to compact repair a Microsoft Access database is as follows. A major problem with mature Microsoft Access databases is the corruption of large tables within the database when multiple users break it. You may receive various error messages all pointing to the need to compact/repair the database tables. If possible, another reason why, is that you may decide to compact repair your database every day which will reduce the chances of the database breaking and becoming corrupt.

How to Compact / Repair an Access Database

From within the database top menu select “Database Tools” then on the resulting ribbon select “Compact and Repair Database”. If all users are out of the database, it will be repaired within a time proportional to the size of the database.

Issues With Compact and Repair Access Database

The main issue is that all users must exit the database before a compact repair can be carried out. There can be major issues with compacting repairing a multi-user Microsoft Access database. You may already know how inconvenient and time-consuming this can be, especially with multiple users in different locations. Before compact/repairing the database, each user has to exit the database, so someone has to message/email every user. Usually, some users are logged in but are not at their desks and may have even gone home. This can result in hours of lost time before the compact repair can be carried out.

Solution for Exiting All Users To Allow Compact Repair

Where all Microsoft Access database front-ends point to a server-based single database back-end, a mechanism can be added to the back-end tables that tells all of the front-end user databases to remain open and operational. Should it be decided that a compact repair is necessary, the back-end mechanism is triggered by an admin person which displays a warning message to all front-end users before closing down all of the front-ends. The front-ends close themselves down as a result of an instruction from the back-end tables to which they all point. Please click here for a more detailed description of the solution.