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.
[Solved] Access2Base Counter for OpenRecordset usage too low
[Solved] Access2Base Counter for OpenRecordset usage too low
- Attachments
-
- OpenRecordset error after opening and closing 32767 Recordset objects
- Screenshot from 2018-11-10 16-54-21.png (8.5 KiB) Viewed 2356 times
Last edited by tsultana on Wed Nov 14, 2018 6:55 pm, edited 1 time in total.
LibreOffice 6.1.3.1 Debian Linux 4.9.0-8-amd64
Re: Access2Base Counter for OpenRecordset usage is too low
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
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
LibreOffice 6.1.3.1 Debian Linux 4.9.0-8-amd64
Re: Access2Base Counter for OpenRecordset usage is too low
@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
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
Kubuntu 22.04 / LibO 24.2
Access2Base (LibO).
BaseDocumenter extension (LibO)
ScriptForge (LibO)
Documentation on https://help.libreoffice.org/latest/en- ... bPAR=BASIC
Access2Base (LibO).
BaseDocumenter extension (LibO)
ScriptForge (LibO)
Documentation on https://help.libreoffice.org/latest/en- ... bPAR=BASIC
Re: [Solved] Access2Base Counter for OpenRecordset usage too
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
Thanks again for all the help.
Tony
LibreOffice 6.1.3.1 Debian Linux 4.9.0-8-amd64