Compact and Repair an Access Database

by | May 19, 2020 | Databases, Step-by-step

The Problem

Access databases are designed to grow as they are used.  As additional records are added the database grows.  By design, when records are deleted, the space allocated for those records is not recovered.  Consequently, the backend database periodically should be compacted.

Databases are also subject to corruption.  For example, if a system experiences a sudden shutdown due to power failure, records in the open database can become corrupt.  Similarly, corruption can occur when a workstation runs out of hard disk space, a user suddenly turns off their computer while the database is open, a user uses task manager to close the database because Windows says Access is not responding, and when intermittent network connectivity issues arise.  Consequently, the backend database should be periodically repaired.

The Solution

Fortunately, Microsoft Access has a built-in Compact and Repair utility.  You can follow the instructions to repair a corrupt Access database.

  1. If you have the database open, close it.
  2. Go to the folder where the database resides.
  3. Determine if an .laccdb file exists, and if so, delete it.
    What is an laccdb file?  It is a Record-Locking Information file.  It keeps track of who has which record opened so it can help prevent users from overwriting each other’s work.
    If the.laccdb file is present, it usually means someone still has the database open.If you get a message like the one below, you will need to click cancel, and then find out who still has the database opened and ask them to close it.
  1. Open the database.
  2. From the ribbon’s Database Tools menu, in the Tools group, click Compact and Repair Database.The system will begin to Compact and Repair the database.  You will see a message and progress bar in the bottom of the screen showing the progress of the compact and repair procedure. When the compact and repair process is complete the system will reopen the database.