How can I resolve Query cannot be completed error in Access 2016?

Question

Hi,

Is anyone knows about “Query cannot be completed” error in Access 2016. I am frequently getting this error when I try to run a query having name field from 5 different tables, it gives flowing error:

 "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."

I don’t have any idea that how can i fix this issue. If anyone knows than please share your ideas. 
 

Answer 1

Hi ….Erik,

Actually the problem is just because of the file size of 1.7 GB. While running a query often needs temporary storage. In your case this means that the normal size plus temp size hits the 2 GB file size limit of access.

Well Access 32 bit and 64 bit version have the same file size limitations. As for the exceeding are you just running some queries or are your sending the results to some temp tables first? One thing that you can try is turn off row locking as that can cause huge bloating. However if you are not sending the data to any temp tables then this suggestion may not work. However if you are using temp tables then I would consider sending the data to an external ACCDB file that is created each time  this would surely eliminate this issue.