How to Fix Access Compact and Repair Fails with Query Size Error
Question details
The user is unable to complete a Compact and Repair operation on a 1.6 GB Access ACCDB backend, receiving an error about exceeding the 2 GB limit or insufficient disk space.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Performing routine database maintenance via Compact and Repair on a large backend file in a recent Microsoft 365 environment.
- Observed behavior
- The Compact and Repair operation fails with a query size error, even though the database is 1.6 GB. The issue appears to be a version-specific bug in recent Microsoft 365 builds, as it did not occur in earlier builds.
Ensure you create a secure, physical backup copy of your ACCDB backend file before attempting any further repair, downgrade, or database conversion operations.
Roll Back to a Previous Stable Access Build
Since this failure is tied to a version-specific bug in recent Microsoft 365 updates, reverting your Office installation to an earlier, known-working build is the most effective immediate fix.
If importing tables into a new database or reducing the file size does not resolve the error, the issue is likely rooted in the current Microsoft 365 Current Channel build handling large queries poorly.
Open Microsoft Access, navigate to File > Account, click on 'Update Options', and select 'Disable Updates' to prevent Office from immediately reinstalling the problematic build.
Open the Command Prompt as an Administrator and use the Office Deployment Tool to execute a rollback command specifying the target version number of your previous stable build.
Once the downgrade is complete, open your backup ACCDB file in the older Access version and navigate to Database Tools > Compact and Repair Database.
Migrate the Backend to SQL Server Express
To completely bypass the strict 2 GB file size limitation and version-specific Access bugs, migrate your heavy backend tables to a more robust database engine.
Discover WPS Office as a Lightweight Alternative
While WPS Office does not handle complex relational databases like Microsoft Access, it serves as an excellent, free Microsoft Office alternative for your everyday document, spreadsheet, and presentation needs. Export your database query results to WPS Spreadsheet to handle large datasets effortlessly without expensive subscription costs.
- 1. Download the Installer: Visit the official WPS website and click the Download button to get the free installer.
- 2. Install WPS Office: Run the downloaded executable file and follow the on-screen prompts to complete the setup.
- 3. Open Your Files: Launch WPS Office to instantly open, edit, and save your existing Word, Excel, and PowerPoint documents.
Frequently Asked Questions
Why does Access trigger a 2 GB limit error when my database is only 1.6 GB?
During the Compact and Repair process, Microsoft Access creates temporary system objects, hidden tables, and queries that temporarily inflate the total database size. If the combined size of your 1.6 GB file and the temporary data exceeds the hard 2 GB limit, the operation will fail.
Should I split my database into multiple files to fix the query size error?
While splitting databases is a standard practice to manage file size, it may not be viable if your Access application relies heavily on cascaded updates and deletes across tables. Those features generally require the linked tables to reside in the exact same backend file.
How can I report this version-specific Compact and Repair bug to Microsoft?
You can report the issue directly through the Access application by navigating to File > Feedback > Send a Frown. Be sure to include your exact Microsoft 365 Current Channel build number, the database size, and details about the Compact and Repair query error.
