Cannot open any more databases. (Error 3048)

[fastrunr]

Hello there,

I am trying to run a fairly elaborate union query in MS Access 2000 and am getting the following error message: "Cannot open any more databases." (Error 3048). I did a couple of google searches that have led me to think it has something to do with the large number of tables being pulled into the query, but all the solutions that I've found are VB-related, and I am not using VB at all, just SQL view in access.

Can anyone offer any suggestions??

Thanks in advance!

 

[Trendsetter]

I would suggest breaking the query down into smaller bits and combining the smaller units in place of the large tables.
This way you can step through the build and see if it falls over at a specific point!

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[fastrunr]

Thanks for replying. I've tried your suggestion, isolated where it breaks down, and don't know where to go from there, partly because I'm not quite sure what the problem is. Is there a limit to the number of tables you can pull into any one query. I have a ton of tables in this query, so if there's a limit, I'm probably exceeding it in the final piece of my union query.

But is there a way around this limit??

 

[Trendsetter]

If you go to the Help for the version you are using and in the index section enter - specification - this will give you the maximums applicable to your version of Access.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[fastrunr]

Hey, what do you know - you're right! It looks like the maximum number of tables in a query allowed by Access 2000 is 32. So is there any way around this, or should I just focus on streamlining my query?

By the way, thanks for responding to this!

 

[Trendsetter]

I would look at the structure of your query.

I have been building Access databases for many years and have never used that many tables for a query!
That really is an enormous amount of information to be dragging across a network........

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[fastrunr]

Well, actually, it's only like 4 or 5 tables. But because the query is a union query it calls the group multiple times, either individually, or united in the form of another query.

I was doing summary statistics on data. I am using this query to compile the various statistics (in a "StatName" field) and am then presenting them in a crosstab query. There are about five different summary statistics (# samples analyzed, # of detects, etc.), each representing part of the union query.

Each of these components is pulling from a previously-assembled query which selects the specific data set I want based on criteria from about 4 linked tables.

I know this is a really rudimentary explanation, but it seems like a pretty standard thing that I'm trying to do. Do you see any obvious ways to streamline??

Thanks again!!

 

[Trendsetter]

Is t possible to write the interim results to a temp table whilst the remainder of the query is then processed based on the temp table contents.
This would speed up complex calculations as it is only having to do it once to build the table.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk