Error 3048: Cannot open any more databases (1 Viewer)

Dwight

Registered User.
Local time
Today, 10:10
Joined
Mar 17, 2003
Messages
168
My database sometimes produces Error 3048: Cannot open any more databases. After researching this on the Internet I now know that it is because Access has an internal limit of 255 simultaneous connections to Jet. A “connection” includes things like combo boxes, recordsets, queries, etc…..and these add up faster than one might think.

I believe the culprit may be a particularly complex report my users frequently run. It is composed of several subreports and each subreport is based on several nested queries.

I used nested queries a lot throughout my database. And sometimes these nested queries contain subqueries further increasing their complexity.

For example, the complex report probably requires at least 30 queries to run to produce the output. My questions are:

1) Are all 30 “query connections” open while the report is open? Or are only the final queries in the nested series open?

2) I could create temp tables and append the data to the table and then pull it out with a query and base the report on this. Would this reduce the number of connections because only the queries pulling the data from the tables would be “open”? (Generally, temp tables seem to be frowned upon by the experts on this forum)

Sometimes I do use recordsets and I have also gone back through to make sure that each time I open a recordset it gets closed e.g., rst.close.

Thanks.
 

KenHigg

Registered User
Local time
Today, 06:10
Joined
Jun 9, 2004
Messages
13,327
a. I'm not sure the number of queries running is producing the error, but my quess is that you're on the right track.

b. Doing the temp table sounds like a good idea.

c. With 30 queries, I would bet that you may have normalization issues with your table layout...

Just my 2 cents :):):)
 

Dwight

Registered User.
Local time
Today, 10:10
Joined
Mar 17, 2003
Messages
168
Thank you for the reply Ken. I will give the temp tables a go and see what happens. If it does not help I can always remove them.

I think I have done a good job of normalization. My database has 109 separate tables. But I'm sure I could have done some things more efficiently than I have......but that's always the case. Always learning new tricks.

Thanks again.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:10
Joined
Feb 28, 2001
Messages
27,001
1) Are all 30 “query connections” open while the report is open? Or are only the final queries in the nested series open?

To the best of my knowledge, they are all open at once.

2) I could create temp tables and append the data to the table and then pull it out with a query and base the report on this. Would this reduce the number of connections because only the queries pulling the data from the tables would be “open”? (Generally, temp tables seem to be frowned upon by the experts on this forum)

Minor correction: Temp tables are frowned upon by the PURISTS on this forum. I'm a pragmatist.

The guiding principle here is going to be the size of the main table and the nature of the sub-tables and sub-queries. With 109 tables, I'm betting dollars to donuts that a large part of your problem is a lot of lookup tables where you store a code but want to see something longer. True?

If so, you are looking at a difficult problem to fix. Is it the case that you have your "30 queries" as at least 27 or 28 LOOKUPS and the rest some sort of one/many joins for your "real" data? Asked another way, how many of those 30 queries are NOT lookup queries?

Another issue can become significant. How many records are we looking at in the main report - and the sub-reports, too? Are the sub-reports your way of handling the one/many JOINS that are some of the 30 queries you describe?

Another issue: record size. Your solution has to take into account that you cannot exceed a certain record size, so you might not be ABLE to pre-populate your TEMP table with everything you need. Size matters! (Sorry guys, I know what we tell the ladies...)

I guess I need to also ask if this humongous report has EVER actually run? If so, what was the last change you made that caused it to no longer run? Adding another lookup?
 

Dwight

Registered User.
Local time
Today, 10:10
Joined
Mar 17, 2003
Messages
168
Doc Man – Thank you for sharing your knowledge. I always learn a lot from your posts.

First of all, the temp tables fixed the problem. The report now runs smoothly.

Yes, my database contains a lot of lookup tables. Shouldn’t that be the case in a properly normalized database?

The report is an asset statement. It has several sections for different asset classes. Each section has unique headers and is actually a subreport. 5 – 6 queries are required to generate each section. Thus, the master report is comprised of over 30 queries.

The total number of records is not many – maybe 50 max.

Printing the report always caused the error (it seems Access runs the report again to print it????) but now it runs and even prints. What more could my users want? :)

Thanks for following up.

Dwight
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:10
Joined
Feb 28, 2001
Messages
27,001
Creating a temp table to reduce the number of open tables is a good solution and as a pragmatist I don't disagree with it. More than anything else I was worred that with ENOUGH lookups, you would run into something that would overflow Access buffer size due to limits on the size of a record. But if you got it working, that obviously didn't happen. Just remember to clean up after yourself when you create the temp table. Follow the basic rules

1. If you open it, close it.
2. If you scatter it, pick it up when you are done.
3. If you create it, delete it when done.

(Variations on advice found in "Everything Important I Needed to Know I Learned in Kindergarten.")
 

Users who are viewing this thread

Top Bottom