Split Database Issue - Cannot Open Anymore Databases (1 Viewer)

[NSAMSA]

Hi:

I'm having an issue after splitting a public database. As a precaution I chose to split a shared database on my company's shared drive to ensure the data would not be corrupted.

Within this database, I had a report based a Union Query and within this report, I have a button which has the very simple command of View in Print Mode. This worked fine before the split, but now it returns the error "Cannot Open Anymore Databases"

The query only utilizes 4 separate tables and doesn't seem overly complicated, (I have about 7 Union All queries to capture different criteria for a particular column). If I open the report initially in Print Preview, it works fine, but switching to a different view is what prompts the error. How can this be fixed?

 

[Ranman256]

if you are using functions in the query, it could produce this error.
try to simplify the queries.
or
use a macro to run several append queries to a 'temp' table. (equivalent to a union query)

 

[sneuberg]

The following from this thread is kind of interesting:

The wording of this error message is misleading. It has nothing to do with how many distinct databases you have open. What it is actually talking about is an internal array or collection of what are called TableIDs. Every open recordset -- not just ones that you open, but all the ones that Access is using to load forms, reports, combo boxes, list boxes, etc. -- uses up at least one TableID. And if you are using linked tables, IIRC, each recordset opened on a linked table uses 2 TableIDs.

I don't recall the exact number, but I think a maximum of something like 2000 TableIDs can be open at one time. If you exceed that limit, you get the "cannot open any more databases" error. You might think, "Pfft! How can I possibly use up 2000 TableIDs?" but it's not uncommon for a complicated application with lots of complex forms & subforms open at once to bump into this limit.

In your case, since the problem only shows up when you open report in Report view, I have to wonder if the issue arises because data in Report view is "live" -- I *guess* that Access is keeping a lot of recordsets open at once, rather than opening a recordset, formatting the data, and closing the recordset. That's only speculation, of course, because I haven't investigated the way Report View is implemented.

Does your report or its subreports use combo boxes to display related data? If so, you may be able to relieve the problem by getting rid of the combo boxes and adding a join to the related table directly to the report's recordsource query. That would eliminate the need for Access to open another recordset (using up 2 TableIDs) to support each instance of each combo box. Similarly, if you use DLookups to get values on the report, you would want to replace them with joined tables to supply the data directly in the recordsource.

Another, though less likely, way you might be using up TableIDs is if you have code behind the report that opens recordsets and (maybe) doesn't close them. I'm not sure how this might occur, however.

 

[moke123]

here's a simple form to give you some insight into your problem.
just import the form into your database and open it. It will give you a running count of open database connections. (See the code comments for Attribution.)

 

[NSAMSA]

Evidently there are 170 database connections when I run the report. I was actually able to fix my particular issue by taking one of the Union queries and changing it to a regular query with an elaborate if/then statement to replace the separate unioned queries.

It actually took it from 170 database connections to 54. I will be careful of this sort of thing in the future. Thank you so much everyone for your help.