If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
error opening form "cannot open anymore databases"
It is a very large form. Is this a memory issue or can I
do something about it. Thanks Martin |
#2
|
|||
|
|||
error opening form "cannot open anymore databases"
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...;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 |
#3
|
|||
|
|||
error opening form "cannot open anymore databases"
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 . |
#4
|
|||
|
|||
error opening form "cannot open anymore databases"
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. martin wrote: 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 . |
#5
|
|||
|
|||
error opening form "cannot open anymore databases"
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. martin wrote: 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 . . |
Thread Tools | |
Display Modes | |
|
|