[Solved] Access2Base Counter for OpenRecordset usage too low

[tsultana]

Each use of OpenRecordset increases the Integer counter RecordsetMax in Access2Base.Database. Once 32767 recordsets are opened an error is reported with the next OpenRecordset. It does not matter that mClose() is used for each OpenRecordset.

32767 may sound large but my use is a converted MS Access database used for Monte Carlo simulations into the millions. I am reworking the logic in many places of my code to use arrays or batch updates but with difficulty.

The following code will error out very quickly.

Dim iRec as Object, x as Long, y as Long
For y = 1 to 100000
x = 0
Set iRec = CurrentDb().OpenRecordset("table")
With iRec
Do While Not .EOF
x = x + 1
.MoveNext
Loop
.mClose
End With
Next

Code runs until the RecordsetMax gets to 32767. Then either
1) run CurrentDb().CloseAllRecordsets() to reset but make sure you are not using any open Recordsets or
2) Close all LO products and reopen Base

This problem is on Debian Linux LO 6.1.3 and Win10 LO 6.1.1

If an enhancement can be done I suggest either;
1) Allow more Recordsets to be opened. A counter that is Integer for RecordsetMax is too low, perhaps changing to Long. I do not know if the Integer limit is carried into other tables or classes to make this practical.
2) The other option is to reuse OpenRecordset counters or at least provide a way to view the RecordsetMax variable as a property call for error trapping. As it currently is the database cannot handle more complex uses without coding to its deficiencies.

[tsultana]

I mentioned using CurrentDb().CloseAllRecordsets() to reset the RecordsetMax = 0 but that does not work. Since I close each Recordset the Collection is empty so the routine exits without resetting the RecordsetMax variable. Below is the code for the CurrentDb().CloseAllRecordsets() subroutine which exits at the If IsNull() line.

Public Sub CloseAllRecordsets()
' Clean all recordsets for housekeeping

Dim sRecordsets() As String, i As Integer, oRecordset As Object
On Local Error Goto Exit_Sub

If IsNull(RecordsetsColl) Then Exit Sub
If RecordsetsColl.Count < 1 Then Exit Sub
For i = 1 To RecordsetsColl.Count
Set oRecordset = RecordsetsColl.Item(i)
oRecordset.mClose(False) ' Do not remove entry in collection
Next i
Set RecordsetsColl = New Collection
RecordsetMax = 0

Exit_Sub:
Exit Sub
End Sub ' CloseAllRecordsets V0.9.5

Changing the first two If statements to reset the RecordsetMax = 0 would fix the issue but may have other side effects that I am not aware of.
If IsNull(RecordsetsColl) Then
RecordsetMax = 0
Exit Sub
End If
If RecordsetsColl.Count < 1 Then
RecordsetMax = 0
Exit Sub
End If

[JPL]

@tsultana

thank you for having reported this limitation (even if opening > 32K recordsets in the same macro is rather unusual ... and not anticipated by me !).

Anyway Access2Base should not fall in error.

The correction can be followed on BUGZILLA.

regards.
JPL

[tsultana]

An update should be in LO 6.2 to increase the limit of OpenRecordset() to 1,000,000 from 32,767. This should be high enough unless someone has an LO Base processing continuously.

Thanks again for all the help.

Tony