MS Access - Compact and Repair Large Database

Question

I have a fairly large (1.36 GB) access database I need to run the "Compact and Repair" database tools on. Smaller databases (less than 500 MB) work without issue.

It starts out fine, but I get an error popup that says: "The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB) or there is not enough temporary storage space on the disk to store the query result".

According to Microsoft, Access creates a copy of the Database, then runs the utility so it could be that in order to compact, the database has to be under 1 GB?

Is there a way to run compact and repair on a large database? Why could you create a database that's 2 GB in size, but can't run a built-in utility on it?

question from:https://stackoverflow.com/questions/65893574/ms-access-compact-and-repair-large-database

Answer 1

The only method seems to be:

1. Create a new empty database
2. Import all objects from the current (too large) database
3. Copy specialities like custom properties (if any) from the current database
4. In the new database, do Compile and Save the code modules
5. Check everything, just in case. However, there should be no issues