Why Access returns: Cannot open anymore databases

[mrchonginhk]

I tried to run a series of queries on Access and the first 6 ones run smoothly but the 7th one gets this error:-


"Cannot open anymore databases"

How to deal with this ? Pls help

 

[Andrew Fergus]

Hi

Can you provide some more information? For instance :

Is this a split database?
Are the queries being run in VBA or from a form etc?
Are the 7 queries related to each other in some way, or are they independent?

Regards
Andrew

 

[mrchonginhk]

No VBA, just SQL.

2 depends on 1, 3 depends on 2 etc

Help ....

 

[Andrew Fergus]

Hi

A couple of things come to to mind. Either you have breached the limit for the number of tables for a query (which is 32) or you have breached the number of levels of nested queries (which is 50). If the 7th query is based on the first 6 queries, then if you add up the number of tables and/or queries, does the number exceed either 32 or 50? The number of tables can be easily breached where you have a whole lot of lookup tables (i.e. tables that store key values that you then use to populate another table).

Can the queries be simplified in some way? Can the functionality in one query be pushed back into a previous query?

I will probably regret asking you to post the details for each query but I think it is the possibly the number and complexity of the queries that is causing the problem. Have you considered pushing the data from the 6th query into a temporary table and then making the 7th and later queries work off that table? It's not ideal I know but if you use a temporary table then you will need to make sure it is correctly cleared out and populated each time you want to run the 7th query.

Andrew