ERROR "Cannot open any more databases"

Discussion:

(too old to reply)

Henry Jordan

2005-02-22 21:23:08 UTC

Received this message when I tried to open a form in ACCESS 2000. Program
operates fine when database and program all on one computer. If I split
database and have backend on another computer and try to access it via local
home network, I get the error message "Cannot open any more databases".
Computer with backend database is not accessing BE database. Form has
multiple subforms on it which access multiple tables. What is the limiting
factor to open databases using a split database?

'69 Camaro

2005-02-27 11:51:02 UTC

Permalink

Hi, Henry.

Post by Henry Jordan
What is the limiting
factor to open databases using a split database?

TableID's. You've added linked tables, which has put the database "over the
limit."

Error #3048 is usually caused by your database application trying to handle
too many tables at the same time. There are 2048 "table handles," or
TableID's, available for Jet 3.51 and 4.0 (1024 TableID's for Jet 3.0 and
3.5) that Access requires when dealing with each table or linked table.

TableID's are used for the following:

- Tables
- Queries
- Bound forms and reports
- Modules for forms and reports
- Combo boxes and list boxes whose row source is a table or query
- Data Access Objects (DAO) Recordsets in code
- Domain functions, such as DLookup( ), DCount( ), et cetera.

If this error is caused by the application using too many TableID's, then
ensure that every Recordset used in code is explicitly closed and the
Recordset variable set to Nothing before the variable goes out of scope. If
this is occurring only after the report is opened multiple times, then
you'll likely fix the problem by explicitly closing and setting objects to
Nothing in at least one code module.

If this isn't enough to prevent the error message from recurring, then the
alternatives are to reduce the number of combo boxes that meet the above
listed criteria, simplify complex queries, or to unlink some of the tables
(linked tables require twice as many TableID's) and place these tables in
the database file.

Reducing the number of any of the items on the list above will help reduce
the number of TableID's in use. The important thing to remember is the
total number of TableID's in use at the same time needs to be prevented from
exceeding the limit.

This error has also been reported to be caused by implicitly referring to
the value of a checkbox in an If...Then block statement. If this is the
case, then the solution is to explictly refer to this value, instead.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering
on "Answered questions" and quickly finding the right answers to similar
questions.

Post by Henry Jordan
Received this message when I tried to open a form in ACCESS 2000. Program
operates fine when database and program all on one computer. If I split
database and have backend on another computer and try to access it via local
home network, I get the error message "Cannot open any more databases".
Computer with backend database is not accessing BE database. Form has
multiple subforms on it which access multiple tables. What is the limiting
factor to open databases using a split database?

Henry Jordan

2005-02-27 14:29:06 UTC

Permalink

Thank you for the good reply on what the problem is.

Is there any way to have a count of how many "table handles" are being used
so that I can try to cut down to that limit without a major design change by
make a number of changes that may not be required?

Post by '69 Camaro
Hi, Henry.

Post by Henry Jordan
What is the limiting
factor to open databases using a split database?

TableID's. You've added linked tables, which has put the database "over the
limit."
Error #3048 is usually caused by your database application trying to handle
too many tables at the same time. There are 2048 "table handles," or
TableID's, available for Jet 3.51 and 4.0 (1024 TableID's for Jet 3.0 and
3.5) that Access requires when dealing with each table or linked table.
- Tables
- Queries
- Bound forms and reports
- Modules for forms and reports
- Combo boxes and list boxes whose row source is a table or query
- Data Access Objects (DAO) Recordsets in code
- Domain functions, such as DLookup( ), DCount( ), et cetera.
If this error is caused by the application using too many TableID's, then
ensure that every Recordset used in code is explicitly closed and the
Recordset variable set to Nothing before the variable goes out of scope. If
this is occurring only after the report is opened multiple times, then
you'll likely fix the problem by explicitly closing and setting objects to
Nothing in at least one code module.
If this isn't enough to prevent the error message from recurring, then the
alternatives are to reduce the number of combo boxes that meet the above
listed criteria, simplify complex queries, or to unlink some of the tables
(linked tables require twice as many TableID's) and place these tables in
the database file.
Reducing the number of any of the items on the list above will help reduce
the number of TableID's in use. The important thing to remember is the
total number of TableID's in use at the same time needs to be prevented from
exceeding the limit.
This error has also been reported to be caused by implicitly referring to
the value of a checkbox in an If...Then block statement. If this is the
case, then the solution is to explictly refer to this value, instead.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering
on "Answered questions" and quickly finding the right answers to similar
questions.

'69 Camaro

2005-02-28 02:21:01 UTC

Permalink

Hi, Henry.

Post by Henry Jordan
Is there any way to have a count of how many "table handles" are being used

There's no way to be precise, because Access handles the TableID's as
needed. As resources are released, previously used TableID's become
available once again, but not necessarily right away.

One can estimate how many are being used, though. A TableID is used for
each table, each form, each form's module, each table listed in a query,
multiple TableID's for each subform with master and child links (depending
upon the complexity of the query used), two TableID's for each linked table,
and so on.

Behind the scenes of bound main forms with bound subforms, Jet is applying a
join between the two (or do you have more?) tables in a SQL query. Since
there are two TableID's for each linked table, two linked tables in two
queries (for two subforms -- more if there's more subforms) = 2 * 2 * 2 = 8
TableID's (there's some overhead TableID's added to that, too). Now add the
RecourdSource of each of those two subforms bound to a query. If that query
has only one linked table, then 2 * 2 = 4 TableID's, plus the overhead
TableID's.

If you have a lot of complex queries used in the bound subforms, since
there's multiple subforms it's possible that moving just a few of those
linked tables to your current database would make a big difference in the
number of TableID's that your
application is using concurrently.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering
on "Answered questions" and quickly finding the right answers to similar
questions.

Post by Henry Jordan
Thank you for the good reply on what the problem is.
Is there any way to have a count of how many "table handles" are being used
so that I can try to cut down to that limit without a major design change by
make a number of changes that may not be required?

Post by '69 Camaro
Hi, Henry.

Post by Henry Jordan
What is the limiting
factor to open databases using a split database?

TableID's. You've added linked tables, which has put the database "over the
limit."
Error #3048 is usually caused by your database application trying to handle
too many tables at the same time. There are 2048 "table handles," or
TableID's, available for Jet 3.51 and 4.0 (1024 TableID's for Jet 3.0 and
3.5) that Access requires when dealing with each table or linked table.
- Tables
- Queries
- Bound forms and reports
- Modules for forms and reports
- Combo boxes and list boxes whose row source is a table or query
- Data Access Objects (DAO) Recordsets in code
- Domain functions, such as DLookup( ), DCount( ), et cetera.
If this error is caused by the application using too many TableID's, then
ensure that every Recordset used in code is explicitly closed and the
Recordset variable set to Nothing before the variable goes out of scope. If
this is occurring only after the report is opened multiple times, then
you'll likely fix the problem by explicitly closing and setting objects to
Nothing in at least one code module.
If this isn't enough to prevent the error message from recurring, then the
alternatives are to reduce the number of combo boxes that meet the above
listed criteria, simplify complex queries, or to unlink some of the tables
(linked tables require twice as many TableID's) and place these tables in
the database file.
Reducing the number of any of the items on the list above will help reduce
the number of TableID's in use. The important thing to remember is the
total number of TableID's in use at the same time needs to be prevented from
exceeding the limit.
This error has also been reported to be caused by implicitly referring to
the value of a checkbox in an If...Then block statement. If this is the
case, then the solution is to explictly refer to this value, instead.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering
on "Answered questions" and quickly finding the right answers to similar
questions.