472,330 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,330 software developers and data experts.

Cannot open any more databases.

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.

Nov 13 '05 #1
6 18626
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.

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

Nov 13 '05 #3
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.

Nov 13 '05 #4
"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
Nov 13 '05 #5
"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
Nov 13 '05 #6
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

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Debbie Gilbert | last post by:
When running a report..I get the message "Cannot open any more databases." Run-time error 3048... Has anyone seen this message before?
4
by: Koen | last post by:
Hi all, At work I created a database which is really helpful. The database is used by approx 15 users. Everything worked great, until I added...
46
by: Steve | last post by:
Access97 Database The database is split into a frontend and backend and not connected to any other database. The database has an unbound report...
1
by: celtic_kiwi | last post by:
I have an Access 97 database, quite large, and have started getting the error, Error 3048: Can't Open any more databases. -The data is split from...
0
by: Smriti Dev | last post by:
Hi, I am creating a tab form that has some subforms within it as aswell. When I sometimes use the forms, I get a 'cannot open more tables error'...
4
by: carrionk | last post by:
Hi, I'm running a union query (12 unions over the same Object*). I'm getting the following message: "Cannot open anymore databases" * The object...
0
by: Holly Cross | last post by:
I have a report that uses several subreports. The report looks fine in print preview, but when I send it to the printer, I keep getting this message...
0
by: bhipwell via AccessMonster.com | last post by:
I am getting the 3048 error: "Cannot open any more databases." I have a couple forms loaded with tabs covered in combo boxes, list boxes, etc. The...
2
n8kindt
by: n8kindt | last post by:
i'm stuck. i'm using access 2007 on a windows vista machine (i've tried running on xp too, though--same result) and this is the error i keep getting...
0
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.