Recovering resources

[Patrick Jackman]

I understand that each combobox and listbox on a form consumes a resource
type; a database connection I believe.

I also believe that this can be delayed if I open a form with combobox and
listbox rowsources empty and set them programmatically when required by the
user; for example, when they navigate to a tab having these controls.

Having set them, is there a way to recover these resources without closing
the form? Setting the rowsource to vbNullString when they navigate off the
tab does not seem to do this.

I am asking because one of my applications is generating error 3048: Cannot
open any more databases. The application employs form instances and several
forms can present a fair bit of information via delayed-loading tab
controls. If a user navigates to all the tabs of one particular form with 8
instances loaded, the app crashes with the above error.

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774

 

[Allen Browne]

If you have lots of subforms in the pages of a tab control but you only
really need to see one at a time, one approach is to remove all the subforms
from the tab control, shrink its height so it's just tall enough to show the
tabs, and place a single subform control directly on the form itself. Then
use the Change event of the tab control to assign the SourceObject property
of the subform control.

This appears to work like a normal tab, but only one subform is actually
loaded at any one time.

One issue with this approach is that Access will reassign the
LinkMasterFields/LinkChildFields when you change the SourceObject, so you
will need to test for this.

 

[Patrick Jackman]

Good idea Allen!

I use your tab header idea in some situations but it won't work here because
4 of the 12 tabs have multiple control types.

But I could replace 6 listboxes, each on a tab, with 1 tab and 1 listbox and
command buttons or an option group to set the recordsource and column
properties as required.

I trust that you believe this will this actually drop the number of
connections used.

Patrick.

If you have lots of subforms in the pages of a tab control but you only
really need to see one at a time, one approach is to remove all the subforms
from the tab control, shrink its height so it's just tall enough to show the
tabs, and place a single subform control directly on the form itself. Then
use the Change event of the tab control to assign the SourceObject property
of the subform control.

This appears to work like a normal tab, but only one subform is actually
loaded at any one time.

One issue with this approach is that Access will reassign the
LinkMasterFields/LinkChildFields when you change the SourceObject, so you
will need to test for this.

 

[Allen Browne]

Each bound subform requires a connection.

Each list box or combo that has a table/query as its RowSource requires a
connection.

 

[Patrick Jackman]

Any idea why setting RowSource back to vbNullString won't release that
connection?

Each bound subform requires a connection.

Each list box or combo that has a table/query as its RowSource requires a
connection.

 

[Allen Browne]

Partick, I'm not privy to what goes on inside Access at that level.

I can tell you that there are bugs where Access fails to release
connections. If you're serious about tracking that down, here's an example
where that is the case:
http://allenbrowne.com/temp/FreeConnectionBug.zip

 

[Patrick Jackman]

I converted the screen to employ this approach. I traded 6 listboxes, each
on a tab for with 1 tab and 1 listbox and
an option group to set the recordsource and column properties as required.

Unfortunately the database connections are not released. The application
still crashes when the 7th form instance is opened. Each changing of the
listbox rowsource appears to consume and retain a connection.

This seems odd to me because I have search screens that re-set the rowsource
of a listbox and users can perform several hundred rowsource resettings per
session without issue. Although the tables being hit are always the same.

Is there a tool to monitor Jet connections being consumed and released?

Patrick.

If you have lots of subforms in the pages of a tab control but you only
really need to see one at a time, one approach is to remove all the subforms
from the tab control, shrink its height so it's just tall enough to show the
tabs, and place a single subform control directly on the form itself. Then
use the Change event of the tab control to assign the SourceObject property
of the subform control.

This appears to work like a normal tab, but only one subform is actually
loaded at any one time.

One issue with this approach is that Access will reassign the
LinkMasterFields/LinkChildFields when you change the SourceObject, so you
will need to test for this.

 

[Allen Browne]

I don't think JET has such as tool, but there's some code that effectively
does that in this database:
http://allenbrowne.com/temp/FreeConnectionBug.zip

It's actually demonstrating a different bug, but you may be able to use the
same approach to test your case.

 

[Patrick Jackman]

Thanks again Allen. I'll have a look.

I may have reported my results prematurely. The first 4 tabs also had
comboboxes and listboxes. I created a generic routine to load these as Value
Lists and I'm now able to open more form instances before crashing. I'll
complete the testing tomorrow and may be able to report success after all.

Patrick.

I don't think JET has such as tool, but there's some code that effectively
does that in this database:
http://allenbrowne.com/temp/FreeConnectionBug.zip

It's actually demonstrating a different bug, but you may be able to use the
same approach to test your case.

 

[Patrick Jackman]

Does the use of database connections by a client also place resource demands
on any file servers where attached databases reside?

If so, should one expect to see an improvement in file server performace if
an application's use of database connections is dramatically decreased where
there are multiple concurrent users?

Patrick.

I don't think JET has such as tool, but there's some code that effectively
does that in this database:
http://allenbrowne.com/temp/FreeConnectionBug.zip

It's actually demonstrating a different bug, but you may be able to use the
same approach to test your case.

 

[Patrick Jackman]

I am pleased to report that I have been able to achieve a significant
improvement in the number of form instances that my application can load
before Microsoft Access generates error 3048 "Cannot open any more
databases". Allen Browne's suggestions were of great help.

Essentially I created a generic routine to load combobox and some listbox
RowSources as Value Lists rather than Queries. The approach is below.

The problem form also had 8 tabs containing one listbox only. Allen
suggested using 1 tab with 1 listbox and resetting the rowsource as
required. I accomplished this by introducing an option group and coding the
After_Update event as below.

Public Function LookupValueList(ByRef rstrRowSource As String, Optional
ByRef orintColumns As Integer = 2, Optional ofReplace As Boolean = False) As
String
' Comments : Creates a value list for a combobox or listbox rowsource
' Parameters: SQL string to use, columns in list, use Replace if there is
a chance of semi-colons in the data
' Returns : n-column, semicolon-delimited string of field values
' Version : 53.03
'
On Error GoTo LookupValueList_Err
Dim rst As DAO.Recordset
Dim strRS As String
Dim intI As Integer

Set rst = DBEngine(0)(0).OpenRecordset(rstrRowSource)
With rst
Do Until .EOF
For intI = 0 To orintColumns - 1
If ofReplace Then
strRS = strRS & Replace(Nz(.Fields(intI)), ";", ":") &
";"
Else
strRS = strRS & .Fields(intI) & ";"
End If
Next
.MoveNext
Loop
.Close
End With
LookupValueList = Replace(strRS, ",", " ")

LookupValueList_Exit:
On Error Resume Next
Set rst = Nothing
Exit Function
LookupValueList_Err:
Call ErrorHandler(mcModuleName & "." & "LookupValueList", Err.Number)
Resume LookupValueList_Exit
End Function

Private Sub fraDetail_AfterUpdate()
With Me!lstDetail
Select Case Me!fraDetail
Case mcOptApplication
.ColumnCount = 5
.ColumnWidths = "0"";0.75"";2"";1"""
.RowSource = "SELECT tblApplication.ApplicationID.....
Case ...

Patrick.

I understand that each combobox and listbox on a form consumes a resource
type; a database connection I believe.

I also believe that this can be delayed if I open a form with combobox and
listbox rowsources empty and set them programmatically when required by the
user; for example, when they navigate to a tab having these controls.

Having set them, is there a way to recover these resources without closing
the form? Setting the rowsource to vbNullString when they navigate off the
tab does not seem to do this.

I am asking because one of my applications is generating error 3048: Cannot
open any more databases. The application employs form instances and several
forms can present a fair bit of information via delayed-loading tab
controls. If a user navigates to all the tabs of one particular form with 8
instances loaded, the app crashes with the above error.

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774

 

[Allen Browne]

I see you've radically improved this, so great.

I've not actually run tests to answer your specific question, here. If you
do, perhaps you could post your results back for anyone else who is
searching for answers to this.