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

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.

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

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.

Donald and Thomas both gave you the right answer here.

What you're doing works because all of those variables are pointing to the exact same Recordset object in memory. So when you close one, you're really closing the single shared object, which makes all the references effectively closed at the same time.

However, I don't think this is a good long-term pattern. It can become very confusing later because the variable names no longer represent what they originally contained. You're basically reusing one dummy recordset as a placeholder for everything.

Also, an empty query result normally does NOT mean the recordset variable is Nothing. A recordset with zero rows is still a valid open recordset object. You can still safely close it afterward. The variable is only Nothing if the Set statement failed or never happened.

So in most cases, the standard cleanup pattern is still the safest and clearest approach:

If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
End If

I suspect the performance improvement you're seeing probably isn't from the reassignment trick itself. More likely you're just getting cleaner object cleanup and leaving fewer orphaned recordsets hanging around, which absolutely can help stability and performance in Access.

Interesting experiment though. Sometimes frustration-driven coding leads to useful discoveries... and sometimes it leads to tribbles in the engine room. :)

Kind of tedious, but you might like this (if ChatGPT is correct):
https://chatgpt.com/share/69fd37f1-de18-83e9-b5cc-ab2e7b14d144

The explanation to the original problem for me sounds like Z = Y = X=...=C=B=A

I stumbled on another reason for using that approach. I recently found out that we can choose to break on all errors but only yesterday the option can be selected mid-execution when there is a break... but this means all those methods that rely on triggering an error to give an implication of object states now just keep breaking my routines. There are 25 error points for 25 recordsets, and if I had left them to be closed in scattered positions among the codes, I would have a tedious time scrolling through to figure out what is wrong with which recordset.

I have tested the codes with one client case repeatedly using the regular and the unorthodox approach, and so far the latter is completing the execution by half a second faster... :)

I was thinking of monitoring memory use and release but I have found out the GlobalMemoryStatus API does not really monitor specific objects for the memory use...

Richard

Yes I appreciate that it could be nothing. But over time, different challenges keep popping up... I plan to write an all known error trapping function, but so far all the methods I used, including isObject, isnull, = 0, iserror, len(.sourceobject.name) = 0, is nothing, .recordcount, NZ(), but an unset recordset is not caught by any of these. It is an object that is nothing, it is not even empty....and I have yet to find out which of these  should be tested first ... can someone tell e?

That makes sense, but I think part of the problem here is the "Break on All Errors" setting. If you have that turned on, then any technique that intentionally triggers an error as part of testing an object is going to become annoying fast, even if you have error handling in place. That's one of the reasons I generally don't like using errors as normal logic unless there's no better choice.

For a recordset object, the first test should be:

If rs Is Nothing Then

That's the test for whether the object variable has been set. IsNull, IsEmpty, Nz, Len, RecordCount, and similar tests are not the right tools for that. Those are for values, not for determining whether an object variable points to an actual object.

Also, a recordset with no records is not Nothing. It is still a valid open recordset. It just has no rows. In that case EOF and BOF will both usually be True, but the recordset itself still exists and can be closed normally.

So the basic order is:

If rs Is Nothing Then
    ' It was never set, or it has already been set to Nothing
Else
    ' It exists, so now you can work with it or close it
End If

For cleanup, I would still use a small helper routine or a consistent cleanup section instead of assigning all your recordset variables to the same dummy recordset. Your approach may seem faster in one test case, but I wouldn't trust a half-second difference unless you test it repeatedly under controlled conditions. Access can vary by that much for all kinds of reasons, including caching.

The unorthodox method works because all the variables are pointing to the same object, but that's also why I don't like it. It makes the variable names misleading, and that can create confusion later. The safer long-term approach is still to close each real recordset when you're done with it, set it to Nothing, and avoid using intentional errors as your normal way of checking object state.

I think the key distinction here is between the Recordset object itself and the variable that points to it. The variable is really just an object pointer. When you do:

Set rs2 = rs1

you're not creating a second independent recordset. You're just creating another variable pointing to the exact same object in memory. So when one gets closed, they're effectively all pointing to the same already-closed object afterward.

This actually goes back to my old C programming days. You've got the variable itself, and then you've got a pointer to that variable or object in memory. It's similar to passing arguments ByRef in VBA. You're not passing a copy of the variable - you're passing a reference to the original thing itself. That's why changing a variable in one procedure can unexpectedly affect it somewhere else. Multiple variables can all be referencing the same underlying object. Always pass ByVal if you want to be sure not to change the original value.

Another important clarification is that a query returning zero rows does NOT normally result in a recordset variable being Nothing. An empty recordset is still a perfectly valid open object. In that case BOF and EOF are both True, but the recordset still exists and can still be closed normally. So if a recordset variable actually IS Nothing, that usually means the Set statement failed, never executed, or the object reference was cleared elsewhere.

One other thing worth mentioning is that open recordsets consume resources and file handles internally. Access will often clean these up automatically when procedures end or the application resets, but relying on automatic cleanup long-term can still lead to resource problems in larger applications. That's another reason why the classic cleanup pattern is still generally the safest approach.

In most procedures that could produce a logic error, I have a ExitCd: label and code that shuts down in an organized manner. The same code gets hit if the routine shuts down without a logic error (I'm talking errors I trap, not RTEs).

I've been through a few iterations of closing record sets in this section of code. Based upon what ChatGPT pointed out, I'm about to change all the procedures to a simple:

On Error Resume Next
rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
rs3.Close: Set rs3 = Nothing
On Error GoTo Rteh

Why do an if...Nothing test if Access can handle it better?

Thanks for the comments, folks.

Sometimes, the recordset cannot even get past the declaration line of the function so the Resume Next does not get triggered. I tried trapping it using “Is Nothing”, isError, isEmpty, isNull, isObject but to do so systematically I need to know which comes first and is the error list exhaustive (apparently not) .

So I wondered, why struggle with Access? Give it what it wants and set all declared recordsets to the same pointer, so I can close every single recordset certainly.

But I discovered last night a problem that could underlie the errors. I copied from all over, and did not know there are identically named but different methods from the OLE reference. I'll check and post.