Error message: "Too many open databases."

[DavidF]

I have been developing a very large database over the past
5 years. Recently, the message "Too many open databases."
pops up while running a particular report. Searching
Microsoft's website yielded nothing helpful -- only that
there is such an error message, but nothing on it's cause
or solution.

At first, nothing seemed to be happening to the reports so
I judiciously applied On Error Resume Next to eliminate
the annoyance. Now I've come to regret this as
occasionally the lookups don't happen (leaving blank
fields on the reports) when more dynamic filtering is
applied to the report source query.

When I first encountered the error message, I had wished
for a way to increase memory allocation (or some such fix)
to be able to open MORE databases. (By the way, I am only
working with a frontend linked to a backend containing the
tables.)

In reading some of the discussions at this site, I suspect
that my problem may have something to do with using
lookups on my reports rather than performing more links in
the queries (thinking that I would be improving
performance).

Does anyone know the cause of this error. Any help is
greatly appreciated.

Thanks to all who take the time to post their opinions.

 

[John Vinson]

my problem may have something to do with using
lookups on my reports rather than performing more links in
the queries (thinking that I would be improving
performance).

In my experience DLookUp is considerably LESS efficient than queries.
Try basing a couple of the offending reports on a Query joining all
(or at least most) of the required tables; with proper table indexing
I think you'll be pleasently surprised.

John W. Vinson[MVP]
(no longer chatting for now)

 

[DavidF]

Thanks, John

I agree about DLookup. Though sometimes convenient, it did
seem to slow things.

However, I should have clarified that I am using combo
boxes on the reports with a Table/Query record source.

Does the same advice apply?

David

 

[Van T. Dinh]

Do you have any code in the Report or code that is executed when the Report
is opened?

Each WorkSpace can only have 256(?) database objects and Access itself uses
some. If you have code using CurrentDb or OpenDatabase in a loop, this can
creates more database objects than the WorkSpace can handle and you get the
mentioned error message. IIRC in one of my earlier test code, it errors out
at about 252 database objects.

 

[John Vinson]

However, I should have clarified that I am using combo
boxes on the reports with a Table/Query record source.

Does the same advice apply?

Even more emphatically. Each Combo Box has its own recordset (which
must be opened using a new Database object, behind the scenes).
They're useful on forms for selecting values and for displaying
"lookups" without making the form's Recordsource non-updateable, but I
have never seen any good reason to use combos on a Report (well, other
than the convenience of saving a Form as a Report).

John W. Vinson[MVP]
(no longer chatting for now)