File Corruption & Recovery

How to Compact and Repair a Password-Protected Microsoft Access Database with VBA

Steven Parker Oct 20, 2026 868 views

Question details

The user needs to programmatically compact and repair a password-protected Microsoft Access 365 database using VBA.

How to Compact and Repair a Password-Protected Microsoft Access Database with VBA

Product: Microsoft Access 365
Device & OS: not provided
Scenario: Automating database maintenance for a password-protected Access database to reduce file size and fix corruptions.
Observed behavior: Using VBA, the user aims to create a compacted and repaired copy of the current database that safely replaces the original file upon successful completion.

Before you start

Ensure you have the correct database password and exclusive access to the file. Close all active connections to the database before running the VBA script to prevent locking errors.

Solution 1Recommended

Use the DAO DBEngine.CompactRepair Method in VBA

The standard method for compacting and repairing password-protected databases in Access 365, ensuring a safe copy is created first.

This approach uses the DAO DBEngine.CompactRepair method, passing the database password as a parameter.

For Microsoft Access 365, the appropriate DAO version is typically DAO.DBEngine.120. Always compact the database to a new file first before programmatically replacing the original.

Open the VBA Editor

Press ALT + F11 in Microsoft Access to open the Visual Basic for Applications (VBA) editor.

Check DAO References

Go to Tools > References and ensure the 'Microsoft Office 16.0 Access database engine Object Library' (or your corresponding version) is checked.

Write the CompactRepair Code

Use the syntax DBEngine.CompactRepair SourceFile, DestinationFile, , , ";pwd=YourPassword" to compact the database into a new, temporary file.

Verify and Replace

Add file handling code using FileSystemObject to verify the new file was created successfully, then delete the original and rename the new file to the original name.

Use the DAO DBEngine.CompactRepair Method in VBA

Handling Errors: If CompactRepair fails, verify the database path, check for open connections, ensure you have proper file permissions, and confirm the password is correct.

Solution 2

Use the DBEngine.CompactDatabase Method as an Alternative

An alternative method if CompactRepair produces errors, allowing you to specify locale and encryption settings during the process.

Free Microsoft Office alternative

Switch to WPS Office for a Lightweight, Cost-Effective Productivity Suite

While Microsoft Access handles complex relational databases, for your everyday documentation, spreadsheets, and presentations, WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Office. Enjoy seamless format compatibility and a familiar UI without the heavy subscription costs.

1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
2. Install WPS Office: Run the setup file and follow the on-screen instructions to complete the lightweight installation in minutes.
3. Open Your Documents: Launch WPS Office and instantly open your existing Word, Excel, and PowerPoint files with full format compatibility.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx)Lightweight installation with blazing fast startup timesFamiliar ribbon interface requiring zero learning curve for Office usersBuilt-in PDF editing and powerful AI assistance features

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an error when running DBEngine.CompactRepair?

Errors usually occur if the database is currently open or in use by another user, if the password provided in the VBA string is incorrect, or if the file path is invalid. Ensure you have exclusive access to the database before running the script.

Which DAO version should I use for Access 365?

For Microsoft Access 365, the recommended DAO version is DAO.DBEngine.120. Ensure the appropriate Microsoft Access database engine Object Library reference is enabled in your VBA project.

Can I compact the database into the exact same file directly?

No, both CompactRepair and CompactDatabase require you to output to a new destination file. You must programmatically verify the new file was created successfully and then replace the original file to complete the process.

Document & File Issues