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

sql excel ms-access ms-access-2010
Christian picture Christian · Sep 25, 2012 · Viewed 17.8k times · Source

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

msjav picture msjav · Sep 25, 2012

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.

Related questions

Using Excel VBA to export data to MS Access table

I am currently using following code to export data from worksheet to MS Access database, the code is looping through each row and insert data to MS Access Table. Public Sub TransData() Application.ScreenUpdating = False Application.EnableAnimations = False Application.EnableEvents = …

Read More
How to get id of newly inserted record using Excel VBA?

Seems a common enough problem this, but most solutions refer to concatenating multiple SQL commands, something which I believe can't be done with ADO/VBA (I'll be glad to be shown wrong in this regard however). I currently insert my …

Read More
Generate sql insert script from excel worksheet

I have a large excel worksheet that I want to add to my database. Can I generate an SQL insert script from this excel worksheet?

Read More