After compact, get query cannot be completed

Question

I have a back-end database that is set to compact on close (in the database options). Half of the time, this works as expected. The other times, I get this error (copied from the server's Event Viewer):

Microsoft Access

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.

P1: 503183

P2: 16.0.4266.1001

P3: 

P4: 

The database is 705 MB and there is over 840 GB of free space on the disk where it resides. The error happens just before the compact command is done - the old database was copied to dbname.mdb, the old database was deleted, the dbname.mdb database has been renamed. All that needs to happen is for Access to finish closing. Instead, I get the above error. Users can still open the front-end database and attach to the back-end. 

This is a problem in particular for my batch processes. I run an Access script via a command line in a batch file. The batch process will not finish because the database is not closing.

This has been going on intermittently for months. I did not get this error when the database was almost twice the size (I was able to remove a duplicate table).

How do I determine what is happening and how do I fix this!

Answer 1

Hmm, okay what about the Track Autocorrect, is it unchecked?  If yes, you might try importing all Objects into a clean database (make a copy) and try the compact in the clean file.

Answer 2

There are no macros (or anything) set to auto execute.

Answer 3

Do you have something running on open?

Answer 4

This is a back-end database that is connected to regularly but only opened as part of this once a day load process or for maintenance (infrequent). Additionally, the load process starts at 6:10 AM when no one is using the database (outside of our business hours). Therefore, the compact is normally run once a day with no one in it. I do not get this error with users in the database. The only other possible times that someone (meaning a single user with exclusive lock) would go into the database is for maintenance and that is only done with the users out of the database (always check for the lock file before doing any maintenance).

As far as the load process goes (Access macro), it does the following:

• sets database locks to 40,000
• deletes records from two tables (approx 40,000 records from each table, no joins)
• deletes all records from one table (approx 20,000 records, no joins)
• does one make table query (approx 40,000 records, no joins or even a where clause) - query uses a linked table (linked to a flat file on a different server)
• inserts records into one table (approx 40,000 records, no joins or even a where clause)
• inserts records into one table (approx 20,000 records, no joins)
• does a make table query (one record, no joins or even a where clause)
• quits Access

Up to the point where the macro issues the QuitAccess action, everything runs fine with no errors. You see the message saying that the database is compacting. I can go to File Explorer and see the database.mdb being created, my original database being deleted, and the database.mdb being renamed. It even goes back to an Access window and then says that it cannot complete the query. After the database has been copied, deleted, and opened new, what query is it trying to complete?

I just realized that I might not have made this clear. The error happens when I close the database and appears to be doing some final step of the compact. The database has been closed at this point and it is trying to exit out of Access.

Answer 5

This would be one of the many reasons one should not do this.  Trying to run a compact on close risks corruption because it cannot properly complete the task if anyone is in the database.  You would be better of checking for Users and then running your batch file late at night.

That said, something seems odd that grows to the point of needing compacting every day.  Are you running a lot of Make Table queries?