sql - MS Access 2010: "Cannot open any more databases."

Question

While struggling with a single legacy MS Access application I faced this weird error:

Cannot open any more databases.

The application makes extensive use of UNION sql statements. So this seems to cause access hitting the limit of 2048 open tables. Any other chance than getting rid of these unions?

Answer 1

I had this problem when using linked external tables. The limit was reached because about 10 excel files were used by different queries over and over again. So the number of open tables was more or less the product of queries and tables.

I imagine using unions multiplies this problem as well.

The solution for me was to copy linked excel tables into Access native tables first. Then run the very same queries with the native tables.