error opening form "cannot open anymore databases"

  • Thread starter Thread starter martin
  • Start date Start date
M

martin

It is a very large form. Is this a memory issue or can I
do something about it.

Thanks
Martin
 
This error means that you have run out of tableids for open tables. There is
a limit to the number of "tables" that can be open at any one time. In
Access 2002 the limit is 2048. In this context if you have a single table
open in table view you are using 1 tableid, if you have an open query based
on 3 tables, you have used 3 tableids. If you have a form based on the query
and the form has a combo based on a single table, you have 4 tableids (I
think). If the tables are actually linked tables I think it uses at least
one additional table id for each linked table.

Obviously there is no easy way to monitor how many tableids are actually
assigned at any given time. This is tracked internally and not exposed to
the developer. Your only indication is when you get this error. So . . . how
to fix your problem? Look carefully at the form that is giving you trouble.
Is it a complex form with many subforms organized on a tab control? Look for
ways to release some of those open queries and recordsets when they are not
needed. Also, make sure that you are destroying all db object variables when
they are no longer needed.

Also take a look at the following article which explains the problem which
results in a different but similar error.

ACC: Error Message: Can't Open Any More Tables
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q165272
 
Thanks for the prompt reply if I'd make append queries to
a temporary table in the front end instead of all the
queries linked to the back end would that hold the table
ids down enough for it to work?

Thanks
Martin
-----Original Message-----
This error means that you have run out of tableids for open tables. There is
a limit to the number of "tables" that can be open at any one time. In
Access 2002 the limit is 2048. In this context if you have a single table
open in table view you are using 1 tableid, if you have an open query based
on 3 tables, you have used 3 tableids. If you have a form based on the query
and the form has a combo based on a single table, you have 4 tableids (I
think). If the tables are actually linked tables I think it uses at least
one additional table id for each linked table.

Obviously there is no easy way to monitor how many tableids are actually
assigned at any given time. This is tracked internally and not exposed to
the developer. Your only indication is when you get this error. So . . . how
to fix your problem? Look carefully at the form that is giving you trouble.
Is it a complex form with many subforms organized on a tab control? Look for
ways to release some of those open queries and recordsets when they are not
needed. Also, make sure that you are destroying all db object variables when
they are no longer needed.

Also take a look at the following article which explains the problem which
results in a different but similar error.

ACC: Error Message: Can't Open Any More Tables
http://support.microsoft.com/default.aspx?scid=kb;EN- US;Q165272


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

It is a very large form. Is this a memory issue or can I
do something about it.

Thanks
Martin
Click to expand...


.
Click to expand...
 
It might but that wouldn't be my first strategy. Instead, I would start
looking for ways to unbind the hidden or unused parts of the form. If you
have a tab control, anything that is not on the current page can be unbound
by clearing the SourceObject or RowSource (depending on the type of
control).

Consider a form with multiple tab pages - if each of those tab pages
contains a subform Access has to open and load each recordset before it
displays the form. This can quickly exhaust the number of TableIds (it also
causes performance issues). The way to get around this is to use unbound
subform controls on each of the tab pages except the first one (ie clear the
SourceObject property on each). Then create an OnChange event for the
tabcontrol itself that sets the SourceObject property of correct subform
based on the new value of the tabcontrol. Remember that the value of the tab
control is actually the page index of the current tab page where 0 refers to
the first tab page.

When I design a complex for this way, my onChange event also clears the
SourceObject of the subform that is being hidden to make sure that resources
in use are minimized. Here's some sample code - note that on each of the
cases instead of using absolute page numbers I get the page number by
getting the pageindex of a named page. I do this because the page index of a
page changes if you reorder the pages but I tend to leave the names alone
once I've set them. This just reduces maintenance and prevents weird errors
when you reorder the pages but forget to adjust the code.

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks for the prompt reply if I'd make append queries to
a temporary table in the front end instead of all the
queries linked to the back end would that hold the table
ids down enough for it to work?

Thanks
Martin
-----Original Message-----
This error means that you have run out of tableids for open tables.
There is a limit to the number of "tables" that can be open at any
one time. In Access 2002 the limit is 2048. In this context if you
have a single table open in table view you are using 1 tableid, if
you have an open query based on 3 tables, you have used 3 tableids.
If you have a form based on the query and the form has a combo based
on a single table, you have 4 tableids (I think). If the tables are
actually linked tables I think it uses at least one additional table
id for each linked table.

Obviously there is no easy way to monitor how many tableids are
actually assigned at any given time. This is tracked internally and
not exposed to the developer. Your only indication is when you get
this error. So . . . how to fix your problem? Look carefully at the
form that is giving you trouble. Is it a complex form with many
subforms organized on a tab control? Look for ways to release some
of those open queries and recordsets when they are not needed. Also,
make sure that you are destroying all db object variables when they
are no longer needed.

Also take a look at the following article which explains the problem
which results in a different but similar error.

ACC: Error Message: Can't Open Any More Tables
http://support.microsoft.com/default.aspx?scid=kb;EN- US;Q165272


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

It is a very large form. Is this a memory issue or can I
do something about it.

Thanks
Martin
Click to expand...


.
Click to expand...
Click to expand...
 
I have only 1 small hidden form which is unbound on the
whole form. I have many hidden fields on the main form for
linking to the subforms which are unbound and set on form
open. I don't think this is what your talking about is it?
About unused parts can you close queries down after that
part of the form is displayed? If so how would it be done?

Thanks
Martin
-----Original Message-----
It might but that wouldn't be my first strategy. Instead, I would start
looking for ways to unbind the hidden or unused parts of the form. If you
have a tab control, anything that is not on the current page can be unbound
by clearing the SourceObject or RowSource (depending on the type of
control).

Consider a form with multiple tab pages - if each of those tab pages
contains a subform Access has to open and load each recordset before it
displays the form. This can quickly exhaust the number of TableIds (it also
causes performance issues). The way to get around this is to use unbound
subform controls on each of the tab pages except the first one (ie clear the
SourceObject property on each). Then create an OnChange event for the
tabcontrol itself that sets the SourceObject property of correct subform
based on the new value of the tabcontrol. Remember that the value of the tab
control is actually the page index of the current tab page where 0 refers to
the first tab page.

When I design a complex for this way, my onChange event also clears the
SourceObject of the subform that is being hidden to make sure that resources
in use are minimized. Here's some sample code - note that on each of the
cases instead of using absolute page numbers I get the page number by
getting the pageindex of a named page. I do this because the page index of a
page changes if you reorder the pages but I tend to leave the names alone
once I've set them. This just reduces maintenance and prevents weird errors
when you reorder the pages but forget to adjust the code.

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks for the prompt reply if I'd make append queries to
a temporary table in the front end instead of all the
queries linked to the back end would that hold the table
ids down enough for it to work?

Thanks
Martin
-----Original Message-----
This error means that you have run out of tableids for open tables.
There is a limit to the number of "tables" that can be open at any
one time. In Access 2002 the limit is 2048. In this context if you
have a single table open in table view you are using 1 tableid, if
you have an open query based on 3 tables, you have used 3 tableids.
If you have a form based on the query and the form has a combo based
on a single table, you have 4 tableids (I think). If the tables are
actually linked tables I think it uses at least one additional table
id for each linked table.

Obviously there is no easy way to monitor how many tableids are
actually assigned at any given time. This is tracked internally and
not exposed to the developer. Your only indication is when you get
this error. So . . . how to fix your problem? Look carefully at the
form that is giving you trouble. Is it a complex form with many
subforms organized on a tab control? Look for ways to release some
of those open queries and recordsets when they are not needed. Also,
make sure that you are destroying all db object variables when they
are no longer needed.

Also take a look at the following article which explains the problem
which results in a different but similar error.

ACC: Error Message: Can't Open Any More Tables
http://support.microsoft.com/default.aspx?scid=kb;EN- US;Q165272


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


martin wrote:
It is a very large form. Is this a memory issue or can I
do something about it.

Thanks
Martin


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top