Error 3048 - cannot open any more databases

[Jon Ole Hedne]

In a tree-view control, I have 8 forms open at the same time. Each of the
forms have a lot of comboboxes based on queries to the same table. Totally I
have about 200 queries open against the same table. After a short time, I
get the error message: Error 3048 - Cannot open any more databases. A search
on the Web, brings up this:

'This sounds like a resource limitation. Usually the error is associated by
having a large number of table id's open. Try to reduce the number of open
tables (table id's actually). '

I don't understand this. Does this mean that not more than about 200 queries
can be run at a time towards an Access database? Anyone who know about this
limitation? Is the limitation per user or per database? If the limitation is
per database, what should I do if I manage to limit the numbers of open
tables to - let's say 20 for each user. Will the problem then araise if
about 10 users have 20 open queries at a time? I know: It can't be like
this...??

Jon Ole Hedne
Norway

[Steve Jorgensen]

The large number of combo boxes may be the cause of the problem, or it may be
the trigger for another problem. These messages tend to occur in apps that use
DAO in code, and don't close and release DAO objects explicitly and in reverse
of the order in which they were opened - failing to do so can lead to a resource
leak.

Assuming you don't have a leak, since all your combo boxes are referring to the
same table, you could try using a callback function to fill them, and open the
recordset just once in code.

[David W. Fenton]

joh...@online.no (Jon Ole Hedne) wrote in
<L2Kxa.13062$b71.2...@news4.e.nsc.no>:

You don't mention your version of Access, but the original Access97
had a limit of 1024 table handles. Later Jet 3.5 service packs
doubled that.

You have to keep in mind that table handles get used up a *lot*
faster than you'd think. Every table, query, recordsource and
rowsource uses up a table handle.

A form recordsource with two tables in it uses up 3 table handles
(1 for each table and one for the recordsource). If the
recordsource has a table and a query and the query has two tables
in it, you've used up 6 table handles (1 for the table, 1 for the
query, 2 for the tables in the query and one for the recordsource).

Each combo box or listbox uses up one for its rowsource and the
requisite numbers of table handles for the tables/queries in the
rowsource.

So, at minimum, each recordsource or rowsource uses up two table
handles (1 for 1 table, 1 for the recordsource/rowsource). A bound
form based on a single table with 10 combo boxes with rowsources
with only one table will use up 22 table handles.

So, this error basically suggests you're trying to load too much
data at one time.

First, make sure you're running the latest service release of
Access (SR2 in the case of Access97) and the latest Jet service
pack (SP3, I believe). If it's A2K, then I've never seen the error
at all.

Second, make sure you're not using up table handles that you don't
*need* to use, such as subforms on tab pages that are not visible
or lots of hidden forms.

Third, make sure you are using the minimum number of table handles
by trying to avoid large numbers of nested queries. Also, don't use
saved queries for the rowsources of combo boxes or for forms --
just have the rowsources/recordsources be the SQL of the saved
queries.

What you'll find if you go through and to this is not just that
you'll stop bumping up against the table handles ceiling -- you'll
also find that the performance of your application is vastly
improved.

In other words, hitting this error is an indication that you have
major architectural problems with your application.

And, yes, I've been there, done that. And I had it happen before
the Jet 3.5 service pack came out that doubled the table handles. I
fixed it and got the thing working.

That reminds, replication vastly increases the number of table
handles involved, and that was the case with the app where I had
the problem.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Jon Ole Hedne]

The version of Access is 2002, so it looks like I have some problems, yes...

The comboboxes uses sql-strings as recordsource, and the sql is pretty
simple, filtering a few records from one table. And I do not use DAO, just
ADO - remembered to close the recordset when it is finished.

I have now made a procedure where I set the rowsourcetype to Value List, and
fill in the values from VBA-code. Looks like I have solved the problem this
way, but I realy don't understand that I reached the limit so quickly.

All tables are linked from another access-database.

I just found another error: I had used the syntax set rs =
Me.Recordset.Clone in three forms OnCurrent-events without closing
afterwords...

Well - thanks a lot :-)

Jon Ole

"David W. Fenton" <dXXXf...@bway.net> wrote in message
news:937FAC740df...@24.168.128.86...