[Solved] Access2Base Counter for OpenRecordset usage too low

Discussions about using 3rd party extension with OpenOffice.org

[Solved] Access2Base Counter for OpenRecordset usage too low

Postby tsultana » Sun Nov 11, 2018 5:36 am

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.
Attachments
Screenshot from 2018-11-10 16-54-21.png
OpenRecordset error after opening and closing 32767 Recordset objects
Screenshot from 2018-11-10 16-54-21.png (8.5 KiB) Viewed 598 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
tsultana
 
Posts: 4
Joined: Fri Oct 19, 2018 5:17 pm

Re: Access2Base Counter for OpenRecordset usage is too low

Postby tsultana » Sun Nov 11, 2018 6:12 am

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
LibreOffice 6.1.3.1 Debian Linux 4.9.0-8-amd64
tsultana
 
Posts: 4
Joined: Fri Oct 19, 2018 5:17 pm

Re: Access2Base Counter for OpenRecordset usage is too low

Postby JPL » Mon Nov 12, 2018 7:10 pm

@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
Windows 7 / LibO 6.0 / AOO 4.1
Ubuntu 18.04 / LibO 6.1
Access2Base extension (AOO) or standard (LibO).
BaseDocumenter extension (LibO)
Documentation on http://www.access2base.com and http://www.access2base.com/basedocumenter
JPL
Volunteer
 
Posts: 103
Joined: Fri Mar 30, 2012 3:14 pm

Re: [Solved] Access2Base Counter for OpenRecordset usage too

Postby tsultana » Wed Nov 14, 2018 7:00 pm

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
LibreOffice 6.1.3.1 Debian Linux 4.9.0-8-amd64
tsultana
 
Posts: 4
Joined: Fri Oct 19, 2018 5:17 pm


Return to Extensions

Who is online

Users browsing this forum: No registered users and 1 guest