How to Compact and Repair an Access Database

A large Access file can slow down, and a repair attempt can fail if someone still has it open. Here’s how to compact and repair an Access database safely, check the result, and handle common errors without risking your only copy.
Step 1: Check the Database and Identify the Problem
First, work out whether the file needs compacting, repair, or a different fix. Compact and Repair rebuilds the database and clears unused space. It doesn’t compress the values in your tables like a ZIP file.
As you add data or change objects, the file can keep space left behind by deleted records and temporary objects. That space can add to file size. If the database has damage, the repair part may help, but it can’t promise to restore every object or record.
Note what’s going wrong before you make changes. Is the file much larger than usual? Do forms or queries take longer to open? Does Access show an error, close unexpectedly, or refuse to open the file? Write down the full message and when it appears.
At MS Access Expert, I work on Access database repair and optimization. If the file won’t open or keeps showing errors, these database repair decisions and safe maintenance can help you judge when a built-in repair is enough.
Microsoft notes that Access files can grow with use and that Compact and Repair can remove unused space or help with some damage. An interruption during database work can sometimes leave a database corrupt.
If the database holds records you may need to recover, stop before compacting. A repair changes the file. Keep the original intact and make a copy before trying anything.
By now you should know whether you’re trying to reclaim unused space or address a file that may be damaged. That distinction guides the next steps.
Step 2: Back Up the File and Ensure Exclusive Access
Your goal is to protect the original and make sure Access can work on the file alone. Compact and Repair needs exclusive access. If another person or Access process has the file open, the operation may stop with a message such as “File already in use.”
Ask everyone to save their work and close the database. In a shared setup, check that users have closed their local front ends too if they connect to the same back-end file. Make a short maintenance window so nobody starts a new session halfway through.
Next, close Access on your own computer. Use your computer’s file-management tools to copy the database file to a separate folder or backup location. Give the copy a clear name, such as “Database_before_repair.accdb.” Keep this untouched copy until you’ve checked the repaired file and confirmed that its data is sound.
If your system is split, it has a front end for forms, queries, and reports, plus a back end for shared tables. Identify which file needs work. Don’t compact or replace the live back end while staff are using it. Make a copy of the file you plan to repair, then arrange exclusive access to that file.
Also check that you can write to the folder where the database sits. Access needs to create a rebuilt file during the process. If the file is on a shared drive, copying it to a local folder first can help rule out a network or folder-access issue.
Don’t delete a lock file just because it remains after Access closes. First confirm that nobody is using the database and that no Access process is still running. A lock file can point to an open session, so deleting it blindly could put the live file at risk.
By now you should have an untouched backup, a clear target file, and confirmation that other users are out. If you can’t get exclusive access, wait rather than forcing the operation.
Step 3: Run Compact and Repair in Microsoft Access
With a backup in place and everyone out, you can run the built-in tool. If the database opens normally, open it in Access and select Database Tools > Compact and Repair Database. In some versions, you can also find the command under File > Info.
Wait for Access to finish. Don’t close the application or interrupt the process. When it completes, open the database again and check that you’re working with the intended file. Compare its file size with the backup, but don’t treat a smaller file as proof that all data is correct.
If the database won’t open, start Access without opening the damaged file. Select Database Tools > Compact and Repair Database, then choose the database you want to repair. Access can create a compacted copy in the same location. Keep the original and verify the new copy before you replace anything.
By now you should have a compacted file or a clear error message to investigate. If the tool completes, move on to checks before returning the database to staff.
For an advanced workflow, an Access developer can use the VBA method DBEngine.CompactDatabase or another compact-and-repair approach. A command-line run can be launched from a script or another scheduling method, but test it on a copy first. The database must be closed, and the process needs a safe way to preserve the original if the rebuild fails.
Don’t set up an automated job until you know which file it will compact and when users are out. A scheduled task that starts while the back end is in use may fail or disrupt work. For shared databases, plan a maintenance window instead of running a compact each time someone closes a front end.
Step 4: Troubleshoot Errors If Compact and Repair Fails
If the repair fails, don’t keep repeating it on your only copy. Read the full message, then check the likely cause. “File already in use” usually means a user or Access process still has the database open. Ask users to exit, check other workstations, and confirm Access has fully closed.
If Access reports that the operation was cancelled or that you may lack permissions, check that you can create files in the database folder. Try a working copy in a local folder where you have write access.
Then try these steps in order:
- Close every Access session that could be using the file.
- Copy the database to a local folder and run the repair on that copy.
- Check folder permissions and make sure the drive has room for Access to create a new file.
- If the database still won’t compact, try opening a blank database and importing objects from the damaged copy.
Importing objects into a new database may help when the file is badly damaged, but it isn’t a sure fix. Keep the original unchanged. Check the imported tables and relationships first, then test forms, reports, queries, and VBA. If a new blank file cannot import key objects, stop and get advice before trying more repair tools.
At MS Access Expert, I handle Access repair when the built-in tool doesn’t resolve the problem. For an important business file, it’s safer to ask someone to assess a copy than to keep experimenting with the production database.

By now you should have ruled out an open session and a folder-access problem. If repair still fails, preserve the file and treat recovery as a separate task.
Step 5: Verify the Database and Prevent Future Problems
A completed repair is a starting point, not a sign-off. Open the repaired copy and check the parts your team relies on each day. Compare key table record counts with your backup or other trusted records. Run the queries that feed your main reports and confirm totals look right.
Test the main forms and reports. If staff use VBA or macros, run the tasks that depend on them. Check attachments if the database stores them. A file can open while a damaged form, report, or code module still causes trouble.
If everything checks out, follow your normal process to put the repaired database into use. In a split setup, update the correct file and confirm that each user’s front end points to the intended back end. Keep the backup until users have confirmed that their regular work runs as expected.
You can turn on Compact on Close in File > Options > Current Database. This setting applies to the database you have open. Microsoft cautions that compacting on close can briefly disrupt availability in a multiuser database, so it may not suit a shared back end.
For a shared system, plan maintenance for a time when nobody is connected. Keep regular backups and compact only the file that needs it. If the database grows quickly again, investigate the cause rather than treating repeated compaction as the whole fix. The Access Database Health Check from MS Access Expert can help flag file size and other common concerns.
By now you should have a tested copy and a safer maintenance plan. If your team needs to compact often, review how the database is structured and how users connect to it.
Frequently Asked Questions
What does Compact and Repair do in Access?
Compact and Repair rebuilds an Access database and removes unused space in the file. This can reduce file size and may help with some database damage. It doesn’t compress the stored data like a ZIP file, and it can’t guarantee recovery from severe corruption. Back up the database first, then check important data and objects after the operation.
Can I compact an Access database while someone is using it?
No. Compact and Repair needs exclusive access to the database file. Ask users to save their work and close Access before you begin. For a shared back end, schedule a maintenance window and confirm that no sessions remain open. Don’t delete a lock file unless you’ve confirmed that nobody is connected.
Why does Compact and Repair say the file is already in use?
The message usually means Access can’t get exclusive access. A user may still have the database open, or Access may still be running on a workstation. Ask everyone to exit, check for open Access processes, and try again. If it continues, work from a copy in a local folder after confirming the original is not in use.
What should I do if Access can’t repair my database?
Keep the original untouched and stop repeating repair attempts on that file. Try a copy in a local folder, check permissions, and then consider importing objects into a new database. If important tables or objects won’t transfer, get help with recovery. Compact and Repair may not fix severe corruption, so don’t assume a failed attempt means the data is gone.
Conclusion
Back up the file, get exclusive access, run Compact and Repair, then test the database before putting it back into use. If you’re unsure whether the file needs repair, start with these signs that an Access database may need repair and decide on your next step from there.
Related Services
Repair a Corrupt Database
Learn more →Recover a Database
Learn more →Troubleshoot Database Issues
Learn more →Related Articles
Fix Access Error 3167: Record Is Deleted (Linked Tables)
Seeing Access error 3167: Record is deleted on linked tables? I explain what causes it, how to fix it, and how to stop it coming back for good.
Read article →Common Causes of MS Access Database Corruption Explained
The common causes of MS Access database corruption, why they happen, and practical steps to prevent losing your data. Plain-English advice from a consultant.
Read article →MS Access Database Backup Strategy for Small Businesses
A practical MS Access database backup strategy for small businesses: what to back up, how often, where to store copies, and how to test that they actually work.
Read article →Still stuck on this?
Send me the details and I'll take a direct look at your database and give you a straight answer.