File Repair

How to Run the Compact & Repair tool to Optimize Database Efficiency

Summary: The limit of Microsoft Access database files (.accdb or .mdb) is 2GB. Exceeding this file size limit can cause performance issues and increase the possibility of database corruption. It is recommended to compact and repair an Access database to reduce its size. Learn more about the benefits of compacting and repairing an Access database, which helps optimize database efficiency. Also, find information on how often you should compact and repair the database and how to do it.

Table of Contents
Benefits of Compacting and Repairing an Access Database
How Often Should I Compact and Repair an Access Database?
Steps to Compact and Repair a Database
  • Before You Proceed
  • Manually Compact and Repair a Database
  • FAQs

    You may have a few tens of hundreds of thousands of records while creating an Access database file (.accdb or .mdb). But the database file may soon approach the 2GB limit degrading performance. Also, a large database file is prone to getting corrupt or damaged. Running the Compact and Repair tool can help fix these problems: it improves database efficiency and fixes a corrupted database. Let's discuss these benefits and others in detail.

    Benefits of Compacting and Repairing an Access Database

    The Compact and Repair tool helps clear storage space, eliminates temporary data, and fixes corruption errors to help improve the efficiency of an Access database. Let’s explore all these benefits in detail:

    • Frees up Storage Space

    Running the Compacting and Repair tool helps compress unused space.

    Let's say you got a table with three records in it. If you delete one of the records, there will be a gap. That's because Microsoft Access marks a record as deleted but doesn't reclaim the space, and the data still sits there. When you add your next record, new space gets added to the end of the table. The more you add and delete records, the more gap is created, and MS Access is not good with re-using space.

    For example, if you add 200 MB of records and delete 100 records, then add another 100 MB of records, you'll have 300 MB of storage space wasted in your database. So, compacting an Access database is important as it gets rid of all that space from all those deleted records, and you'll have a fresh database file with no empty spaces or gaps. This will reduce the size of your database and makes it efficient. And, of course, a small database ensures faster performance both on a computer and on a network share.

    Note: You must also consider splitting your database if it is approaching the 2GB limit. Split your database with multiple users in a front-end and a back-end database.

    • Gets Rid of Junk

    Microsoft Access doesn't clean up junk. There's an index file and temporary objects like tables that Access doesn't clean up itself automatically. So if you don't compact your database frequently, it keeps getting bigger, and performance keeps getting lower. However, compacting and repairing a database once in a while can do away with the junk.

    • Fixes Database Corruption

    The Repair part in the 'Compact and Repair' tool can sometimes fix small problems before they get bigger. For instance, long text fields can't take more than 255 characters, resulting in database corruption. Running the Compact and Repair Database command periodically ensures to fix the long text field and other issues that could damage or corrupt a database.

    Tip! If the ‘Compact and Repair’ utility fails to fix a corrupted database, using an Access database repair tool can save time troubleshooting and restoring the database to its original form.

    free download

    How Often Should I Compact and Repair an Access Database?

    You should compact and repair depending on how often you use your database. If you are using the database on your own daily, compacting it every week would be sufficient. If you got a database on the network that multiple users access, the database could easily grow in size. So, consider compacting the database regularly.

    Steps to Compact and Repair a Database

    You can compact and repair a database using different approaches. One approach is to run the compact and repair tool when the database closes automatically. Or, you can manually compact and repair an open database or a database that won't open.

    Note: Avoid selecting the option to compact a database when it closes for multi-user databases, as it can disrupt database availability and slows you down. Also, the ‘Compact on Close’ option could make you lose data if it is performed on a database marked as suspect. However, the data may be recoverable before the database is compacted.  

    Before You Proceed

    • Ensure to make a backup first before your compact and repair a .accdb or .mdb file.
    • You must have exclusive access to your database to prevent other users from interfering with the compact and repair process.
    • Ensure you have sufficient permissions to access ACCDB/MDB database file. Also, you must have permission to compact and repair a database.

    Manually Compact and Repair a Database

    The steps to compact and repair an Access database vary depending on whether a database is open or not. For a database that is not open, follow these steps to compact and repair it:

    • Launch your MS Access application, go to File, then click Info.
    • From the ‘Info’ screen, choose Compact & Repair Database.

    A copy of the compacted and repaired database will get saved in the same location as the original DB.

    To compact and repair a DB that is not open, perform these steps:

    • From the templates page in MS Access program, double-click the Blank Database.

    open blank database

    • Click on File, then click Close.
    • Click the Database Tools tab, and click on the Compact and Repair Database.

    compact and repair database

    • From the ‘Database to Compact From’ window, search the database to be compacted and repaired. Press the Compact button to continue.

    database to compact from

    You will find a copy of the compacted and repaired database in the location where the original database is stored.

    FAQs

    Q. I am running an Access database to insert records via a pass-through query. I get some data from an SQL database and dump it in an Access DB. I update the Access DB every week but I cannot append new data from a previous week as all the other past weeks would also get updated.

    So, I planned to truncate the database and load the data again. But this could increase the database size. Is there any other alternative to compacting and repairing the database?

    A. Try moving all truncated tables and other temporary data to a separate database. Once separated, you can replace the database containing unnecessary data with an empty database file. Next, copy and save the empty database over a live (existing) DB with old data. Using this process to reduce DB size works faster than the compact and repair method.

    Q. Can I compact an Access database with VBA code?

    A. Yes, you can compact a database with VBA code.

     

    progress
    76% of people found this article helpful