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.

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" <ultraton@gmail.com> wrote in message
news:1118290490.075323.209580@f14g2000cwb.googlegr oups.com...[color=blue]
> 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.[/color]

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

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" <AllenBrowne@SeeSig.Invalid> wrote in message
news:42a7cec9$0$13571$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> 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" <ultraton@gmail.com> wrote in message
> news:1118290490.075323.209580@f14g2000cwb.googlegr oups.com...[color=green]
>> 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.[/color][/color]

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
news:42a7cec9$0$13571$5a62ac22@per-qv1-newsreader-01.iinet.net.au:
[color=blue]
> The message means that Access is trying to open too many
> connections to the database.[/color]

No always. When you run out of table handles, you can receive the
same error message.
[color=blue]
> Access sets aside memory space for 2048 database connections. . .[/color]

Uh, you mean table handles here, not connections.
[color=blue]
> . . . The
> kinds of things that use database connections include:
>
> a) Forms and reports (including subforms and subreports): close
> any you don't need.[/color]

It's not forms or reports that use table handles, its their
*recordsources*.
[color=blue]
> 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 . . .[/color]

Table handles. The error message is erroneous, as it points to the
wrong problem.
[color=blue]
> . . . were available, so you need to apply SR2.[/color]

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

"ultraton" <ultraton@gmail.com> wrote in
news:1118323293.284324.49260@o13g2000cwo.googlegro ups.com:
[color=blue]
> I believe that (b) and/or (c) are the problem.
> How should it proceed to fix it?
> I am using Access2000.[/color]

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

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" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns96708EB474829dfentonbwaynetinvali@24.168.1 28.74...[color=blue]
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
> news:42a7cec9$0$13571$5a62ac22@per-qv1-newsreader-01.iinet.net.au:
>[color=green]
> > The message means that Access is trying to open too many
> > connections to the database.[/color]
>
> No always. When you run out of table handles, you can receive the
> same error message.
>[color=green]
> > Access sets aside memory space for 2048 database connections. . .[/color]
>
> Uh, you mean table handles here, not connections.
>[color=green]
> > . . . The
> > kinds of things that use database connections include:
> >
> > a) Forms and reports (including subforms and subreports): close
> > any you don't need.[/color]
>
> It's not forms or reports that use table handles, its their
> *recordsources*.
>[color=green]
> > 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 . . .[/color]
>
> Table handles. The error message is erroneous, as it points to the
> wrong problem.
>[color=green]
> > . . . were available, so you need to apply SR2.[/color]
>
> 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[/color]