Cannot open any more databases and splitting databases

[ericgj]

I understand it is a best practice in a multi-user environment to split your
database into a front-end and a back-end.

However, from my experience and from many others I've seen online, doing so
makes you more vulnerable to the "Cannot open any more databases" error due
to reaching the Jet limit of table handles open. Evidently, opening a linked
table is more expensive than a local one. For example, when I un-split my
database, no other changes made, the same form opens without error. In my
case the error has nothing to do with DLookup (which I don't use), or not
closing recordsets in code, or too many query-sourced comboboxes. It has to
do with splitting the database.

I have not seen a satisfactory answer to this (short of moving to a
different back-end database). I am looking at simplifying one of the queries
I use a lot which joins ~10 tables. Although in the short term I think I
don't have a choice but to NOT split the database.

Eric

 

[Tony Toews [MVP]]

I understand it is a best practice in a multi-user environment to split your
database into a front-end and a back-end.

However, from my experience and from many others I've seen online, doing so
makes you more vulnerable to the "Cannot open any more databases" error due
to reaching the Jet limit of table handles open. Evidently, opening a linked
table is more expensive than a local one. For example, when I un-split my
database, no other changes made, the same form opens without error. In my
case the error has nothing to do with DLookup (which I don't use), or not
closing recordsets in code, or too many query-sourced comboboxes. It has to
do with splitting the database.

There is something interesting going on. I've only received this
error occasionally, every few years or so. It's only happened when I
had some very complex reports and many queries executing.

I have not seen a satisfactory answer to this (short of moving to a
different back-end database). I am looking at simplifying one of the queries
I use a lot which joins ~10 tables.

Ten tables isn't a great deal for a query. Is this only just the one
query? Is it run behind a report? Are there many subreports?

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I understand it is a best practice in a multi-user environment to
split your database into a front-end and a back-end.

However, from my experience and from many others I've seen online,
doing so makes you more vulnerable to the "Cannot open any more
databases" error due to reaching the Jet limit of table handles
open.

Only if your app is badly designed to begin with, in my opinion. If
your app without linked tables is that close to the table handles
limit, then you're being very inefficient. Likely causes of this,
for example, would be pre-opening data-bearing forms hidden, loading
subforms on tab pages that aren't visible, populating combo boxes
and listboxes on non-visible tabs, etc. The key is to load data only
when it's being displayed.

There is a trade-off there, though, and that's that there will
delays in switching to tabs that have to load a subform/rowsource
when they are requested.

Another thing that I found long, long ago back in the time when I
first encountered this, was that excessive use of nested queries can
also increase the problem. In the app where it happened to me, I was
using a bunch of shared queries, and nesting them 3 and 4 levels
deep. When I hit the table handles threshold, I got rid of as many
of the nested queries, and put the source tables as close to the
form recordsource as I could. Ideally, I'd use nothing but tables in
a form's recordsource (with not saved queries at all). This made the
app harder to maintain in some respects, but it also made it more
efficient.

[]

I have not seen a satisfactory answer to this (short of moving to
a different back-end database). I am looking at simplifying one
of the queries I use a lot which joins ~10 tables. Although in
the short term I think I don't have a choice but to NOT split the
database.

No, you're simply wrong.

First off, your app should have been split from the beginning if it
was going to be used in a multi-user environment. If you had
developed it using the production architecture, you wouldn't now be
scrambling to fix problems you didn't anticipate -- instead you
would have encountered them and fixed them long ago.

Second, hitting the table handles limit is an indication that you
are opening too many recordsets and your app will be more efficient
if you eliminate the number of recordsets you are opening (as I
outlined above). You should be grateful for this problem as the
result will be a better-designed app that retrieves only the
necessary data when it's needed.

Attributing the problem to splitting is a matter of the carpenter
blaming his tools when the house falls down.

 

[egj_news]

On Dec 17, 11:24 pm, "Tony Toews [MVP]" <[email protected]>
wrote:
....

Ten tables isn't a great deal for a query.   Is this only just the one
query?  Is it run behind a report?  Are there many subreports?

The query is a parameter query run 14 times to populate a 2-week
calendar of tasks, implemented as 14 sub-forms (one per day). In
addition, about 9 equally complex parameter queries are run to
populate various other subforms. I wish there were some documentation
about how many handles are opened in Jet per open connection or
recordset, do you know of such a thing?

Anyway, I've simplified the query a little bit (sacrificing some data
that users can get elsewhere without too much trouble, and some non-
essential lookup tables), and got it to work with a split database,
for the moment. We'll see when I start launching reports if it's lean
enough...

Eric