Closing Recordsets - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Closing Recordsets

Edwin Siew

12 hours ago

I want to share something that I have done that APPEARS to improve the performance of my application but it is quite unconventional and I did it out of frustration without expecting any particular consequence. Maybe there is a catch and the improvements is just my imagination…. I guess sharing here could get some comments in case it is not a good thing to do and could lead to memory leak or something

Sometimes a couple of recordsets not set because no query result etc, and using error inducing test for open recordset got me stuck with not knowing what to do with not-set recordsets. I trried is nothing, isempty, isobject, isnull, iserror.

Out of sheer frustration because I had other things to do and could spend more time on this problem, I decided to give the bugger what it wants, and made all recordets open so I have no need to test for which is open, and may close every one

At the end of the module, I re-set one recordset to query a single row single field table, and set every other recordset = to that specific recordet (not clone, set = directly)

At that stage, it seems I only need to close one of the recordets to close all of them… is this correct result? Would there be any problem memory leak from improper closure?

Then I set each of them to nothing,

The Access project seems noticeably faster and stable in the moments that follow, or maybe I was imagining it.

I wonder if re-setting recordsets to a small memory query result forces release memory? I have tried setting form recordsets the same way but I did not notice any improvement in the operation of the remaining forms/

Anyway, just sharing, and if anyone could let me know there’s any catch or risk in doing this, please let me know.

	Thomas Gonder @Reply 5 hours ago
I'm a little lost in your story. Why aren't you doing this when exiting a function or subroutine (why do you at the end of a module?)? If Not rsR Is Nothing Then rsR.Close Set rsR = Nothing Set db = Nothing

	Edwin Siew OP @Reply 5 hours ago
That's just for cleaning up. Maybe I will do these t at appropriate spots later but while developing, it just so happen this time I put them all in one place for the time being, so I can track that I have closed everyone of them.

Edwin Siew OP @Reply

5 hours ago

Like this:

Set rstQuery = CurrentDb.OpenRecordset("Select * from databaselocation where ID = 1")
Set rstQuery2 = rstQuery
Set rstQuery3 = rstQuery
Set rstQuery2NationalPhaseEntry = rstQuery
Set rstQuery2ExamBegun = rstQuery
Set rstQuery2OA = rstQuery
Set rstQuery2allowance = rstQuery
Set rstQueryprovisional = rstQuery
Set rstQuery5 = rstQuery
Set rstQuery7 = rstQuery
Set rstQuery8 = rstQuery
Set rstQuery10 = rstQuery
Set rstQuery12 = rstQuery
Set rstQuery2searchBegun = rstQuery
Set rstQuery13 = rstQuery
Set rstQuery14 = rstQuery
Set rstQuery15 = rstQuery
Set rstQuery17 = rstQuery
Set rstQuery10extensiondays = rstQuery

rstQuery.Close '<--- this seems to close every one of them.

Set rstQuery = Nothing
Set rstQuery2NationalPhaseEntry = Nothing
Set rstQuery2ExamBegun = Nothing
Set rstQuery2OA = Nothing
Set rstQuery2allowance = Nothin...

What I am interested to know is whether there's hidden problems doing this way, as I have only come across testing dao.recordset to see if it is open in order to close it. What I am proposing is to get it open, so we can close it, no need to test.

I copied the following from somewhere and I have found that is the widespread way of doing it... but if oRs is not set because the query returns nothing, this keeps throwing an error which I do not know how to catch.

Public Function IsRecordsetOpen(ByVal oRs As DAO.Recordset) As Boolean
Dim lRecCounter As Long
lRecCounter = oRs.RecordCount 'Try to use the RecordSet property
IsRecordsetOpen = (Err.number = 0)
On Error GoTo 0
End Function

Donald Blackwell @Reply

3 hours ago

Edwin That last function you provided: IsRecordSetOpen. It is missing some pieces to keep from throwing an error.

Before you check the recordcount, you check to see if the recordset is nothing:

Public Function IsRecordsetOpen(ByVal oRs As DAO.Recordset) As Boolean

    If oRs Is Nothing Then

        IsRecordsetOpen = False

        Exit Function

    End If

And then, there is a line missing to keep the error from popping up if oRs DOES exist:



    On Error Resume Next

    Dim l As Long

    l = oRs.RecordCount

    IsRecordsetOpen = (Err.Number = 0)

    On Error GoTo 0

End Function

Although, if you're just seeing if it's open to close it, it would be easier to just close it without the overhead of checking:

If Not oRs Is Nothing Then

    On Error Resume Next

    oRs.Close

    Set oRs = Nothing

    On Error GoTo 0

End If

This will just close it if it's open and do nothing if it is already closed or if it's nothing. In other cases, the On Error Resume Next censors the error.

Next Unseen

New Feature: Comment Live View