sub5
Programmer
- Oct 12, 2005
- 104
Hi all,
Is using this code enough to clear a collection (the same rst.close and set rst = nothing)?
set cln1 = nothing
Why I ask:
I have stripped out a load of temptables and replaced them with custom collections. Complicated SQL's were used to populate combo boxes and list boxes. Now user defined functions do the job using the collections (see eg of code below). And I have started getting error 3048: too many databases open.
I have also replaced temptextboxes with variables.
I did have 3048 errors before and fixed it by making sure all the recordsets i opened were closed and set to nothing, and reduced the number of forms open at once.
I thought using the variables and collections would make the dbase less prone to 3048 because I have reduced the number of tables and controls on the open form.
I am using set cln1 = nothing, but is this enough?
nb alot of the collections are public and in standalone modules if that makes a difference.
Eg of UserDefined function:
Private Function fncRates4CPTerms(fld As control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
Static arrList() As String, entries As Integer
Dim ReturnVal As Variant, rows As Integer
ReturnVal = Null
rows = clnRates4CPTerms.Count
Select Case code
Case acLBInitialize
entries = 0
ReDim Preserve arrList(2, rows)
Dim instL As Variant
For Each instL In clnRates4CPTerms
arrList(0, entries) = instL.mstrContractKey
arrList(1, entries) = instL.mstrEngDesc
arrList(2, entries) = instL.mstrProdDesc
entries = entries + 1
Next
ReturnVal = entries
Case acLBOpen
ReturnVal = timer
Case acLBGetRowCount
ReturnVal = entries
Case acLBGetColumnCount
ReturnVal = 3
Case acLBGetColumnWidth
ReturnVal = -1
Case acLBGetValue
ReturnVal = arrList(col, row)
Case acLBEnd
Erase arrList
End Select
fncRates4CPTerms = ReturnVal
End Function
Is using this code enough to clear a collection (the same rst.close and set rst = nothing)?
set cln1 = nothing
Why I ask:
I have stripped out a load of temptables and replaced them with custom collections. Complicated SQL's were used to populate combo boxes and list boxes. Now user defined functions do the job using the collections (see eg of code below). And I have started getting error 3048: too many databases open.
I have also replaced temptextboxes with variables.
I did have 3048 errors before and fixed it by making sure all the recordsets i opened were closed and set to nothing, and reduced the number of forms open at once.
I thought using the variables and collections would make the dbase less prone to 3048 because I have reduced the number of tables and controls on the open form.
I am using set cln1 = nothing, but is this enough?
nb alot of the collections are public and in standalone modules if that makes a difference.
Eg of UserDefined function:
Private Function fncRates4CPTerms(fld As control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
Static arrList() As String, entries As Integer
Dim ReturnVal As Variant, rows As Integer
ReturnVal = Null
rows = clnRates4CPTerms.Count
Select Case code
Case acLBInitialize
entries = 0
ReDim Preserve arrList(2, rows)
Dim instL As Variant
For Each instL In clnRates4CPTerms
arrList(0, entries) = instL.mstrContractKey
arrList(1, entries) = instL.mstrEngDesc
arrList(2, entries) = instL.mstrProdDesc
entries = entries + 1
Next
ReturnVal = entries
Case acLBOpen
ReturnVal = timer
Case acLBGetRowCount
ReturnVal = entries
Case acLBGetColumnCount
ReturnVal = 3
Case acLBGetColumnWidth
ReturnVal = -1
Case acLBGetValue
ReturnVal = arrList(col, row)
Case acLBEnd
Erase arrList
End Select
fncRates4CPTerms = ReturnVal
End Function