Cannot open any more databases.

[ultraton]

While trying to print a report from Access the user receives the
following error:

Cannot open any more databases.
Okay Help

Does anyone have any ideas about this behavior?
Thank you very much.

[Allen Browne]

The message means that Access is trying to open too many connections to the
database.

Access sets aside memory space for 2048 database connections. The kinds of
things that use database connections include:

a) Forms and reports (including subforms and subreports): close any you
don't need.

b) Forms/reports that contains lots of list/combo boxes across the record.

c) Forms/reports/queries that use the domain aggergate functions, such as
DLookup(), DMax().

d) Code that opens recordsets, and fails to close them and set the objects
to Nothing.

e) Referring to the RecordsetClone of forms (even where you do set the
objects to nothing.

f) Recursive code that opens recordsets.

b, c, and d are the most common culprits.

BTW, if you are using the original Access 97, only 1024 database connections
were available, so you need to apply SR2.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ultraton" <ul******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

While trying to print a report from Access the user receives the
following error:

Cannot open any more databases.
Okay Help

Does anyone have any ideas about this behavior?
Thank you very much.

[ultraton]

Thank you, Allen.
I believe that (b) and/or (c) are the problem.
How should it proceed to fix it?
I am using Access2000.

[Allen Browne]

For (b), redesign the table. If you have lots of repeating fields, create
another table that contains many records related to the first one instead.

For (c), you may be able to use a subquery, or perhaps a replacement for
DLookup() that cleans up after itself. There's one here:
http://allenbrowne.com/ser-42.html
Or you may be able to move the DLookup() into the Control Source of the text
box on the form, so it is not calculated for all records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:42***********************@per-qv1-newsreader-01.iinet.net.au...

The message means that Access is trying to open too many connections to
the database.

Access sets aside memory space for 2048 database connections. The kinds of
things that use database connections include:

a) Forms and reports (including subforms and subreports): close any you
don't need.

b) Forms/reports that contains lots of list/combo boxes across the record.

c) Forms/reports/queries that use the domain aggergate functions, such as
DLookup(), DMax().

d) Code that opens recordsets, and fails to close them and set the objects
to Nothing.

e) Referring to the RecordsetClone of forms (even where you do set the
objects to nothing.

f) Recursive code that opens recordsets.

b, c, and d are the most common culprits.

BTW, if you are using the original Access 97, only 1024 database
connections were available, so you need to apply SR2.
"ultraton" <ul******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

While trying to print a report from Access the user receives the
following error:

Cannot open any more databases.
Okay Help

Does anyone have any ideas about this behavior?
Thank you very much.

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42***********************@per-qv1-newsreader-01.iinet.net.au:

The message means that Access is trying to open too many
connections to the database.
No always. When you run out of table handles, you can receive the
same error message.
Access sets aside memory space for 2048 database connections. . .
Uh, you mean table handles here, not connections.
. . . The
kinds of things that use database connections include:

a) Forms and reports (including subforms and subreports): close
any you don't need.
It's not forms or reports that use table handles, its their
*recordsources*.
b) Forms/reports that contains lots of list/combo boxes across the
record.

c) Forms/reports/queries that use the domain aggergate functions,
such as DLookup(), DMax().

d) Code that opens recordsets, and fails to close them and set the
objects to Nothing.

e) Referring to the RecordsetClone of forms (even where you do set
the objects to nothing.

f) Recursive code that opens recordsets.

b, c, and d are the most common culprits.

BTW, if you are using the original Access 97, only 1024 database
connections . . .
Table handles. The error message is erroneous, as it points to the
wrong problem.
. . . were available, so you need to apply SR2.

The key is:

Every dataset and data subset uses a table handle.

A form's recordsource uses a table handle.

Each table or query in the recordsource uses a table handle. Thus, a
SQL recordsource with one table uses TWO table handles.

A query used in a recordsource uses 1 table handle for the query and
one for each table/query it includes. A recordsource with one table
and one query will use a minimum of 4 table handles (1 for the
recordsource, one for the table, one for the query, one for the
table that the query is wrapped around).

Then, as you say, each control with a rowsource uses table handles
in the same way.

Add to that the wrinkle of replication, which keeps a whole host of
replication tables open in the background, unbeknownst to you, and
thus adds a bunch of table handles to the process (perhaps even
doubling the number -- I don't know for certain).

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

[David W. Fenton]

"ultraton" <ul******@gmail.com> wrote in
news:11*********************@o13g2000cwo.googlegro ups.com:

I believe that (b) and/or (c) are the problem.
How should it proceed to fix it?
I am using Access2000.

1. Don't populate a form, subform, listbox or combo box until it's
visible. The tab control is a really easy way to control this,
because you can use the tab control's OnChange event to assign the
recordsources/rowsources to the subforms/controls on that tab page.

2. don't populate combo boxes until after the user has typed a
couple of characters, and then remove the rowsource when the control
is exited.

3. don't leave forms open in the background, hidden, unless
absolutely necessary.

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

[PC Datasheet]

I have discovered that procedures in a non-split database use less table
handles than the same split database. Just an observation - I don't know
why.

Steve
PC Datasheet
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.74...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42***********************@per-qv1-newsreader-01.iinet.net.au:

The message means that Access is trying to open too many
connections to the database.

No always. When you run out of table handles, you can receive the
same error message.

Access sets aside memory space for 2048 database connections. . .

Uh, you mean table handles here, not connections.

. . . The
kinds of things that use database connections include:

a) Forms and reports (including subforms and subreports): close
any you don't need.

It's not forms or reports that use table handles, its their
*recordsources*.

b) Forms/reports that contains lots of list/combo boxes across the
record.

c) Forms/reports/queries that use the domain aggergate functions,
such as DLookup(), DMax().

d) Code that opens recordsets, and fails to close them and set the
objects to Nothing.

e) Referring to the RecordsetClone of forms (even where you do set
the objects to nothing.

f) Recursive code that opens recordsets.

b, c, and d are the most common culprits.

BTW, if you are using the original Access 97, only 1024 database
connections . . .

Table handles. The error message is erroneous, as it points to the
wrong problem.

. . . were available, so you need to apply SR2.

The key is:

Every dataset and data subset uses a table handle.

A form's recordsource uses a table handle.

Each table or query in the recordsource uses a table handle. Thus, a
SQL recordsource with one table uses TWO table handles.

A query used in a recordsource uses 1 table handle for the query and
one for each table/query it includes. A recordsource with one table
and one query will use a minimum of 4 table handles (1 for the
recordsource, one for the table, one for the query, one for the
table that the query is wrapped around).

Then, as you say, each control with a rowsource uses table handles
in the same way.

Add to that the wrinkle of replication, which keeps a whole host of
replication tables open in the background, unbeknownst to you, and
thus adds a bunch of table handles to the process (perhaps even
doubling the number -- I don't know for certain).

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