How to check for an open recordset?

[PhoenixofMT]

I have some code that looks something like this:

  If Nz(some_field, "") then err.Raise 94, etc.

  strSQL = "SQL query"
  
  Set db = CurrentDb()
  Set rst = db.OpenRecordset(strSQL)

  bunch more code...

Exit_MyFunction:
  rst.Close              
  Set rst = Nothing
  Set db = Nothing
  Exit Function

Error_MyFunction
  MsgBox err.DESCRIPTION, etc.
  Resume Exit_MyFunction

The problem I have is that if that null check errors out, I get an "Object variable or With block variable not set" error when I hit the rst.Close line in the exit section. This can lead to endless loops.

Is there a way to check that rst is open before I try to close it? I've run into this a couple of times, usually when I comment something out temporarily and it works again when I un-comment it.
In this instance my solution has been to move the null check below the "Set rst =", but I'd like to be able to check it explicitly, rather than be sure my errors will always happen in the right places.

Thanks,

-- Phoenix

 

[LPurvis]

Hi

This does come up from time to time - and it's not a hard and fast answer.
Have a look at some existing threads.
http://www.utteraccess.com/forum/Testing-DAO-Recordset-Ope-t1946173.html
Which links to the more directly applicable
http://www.utteraccess.com/forum/Determine-recordset-open-t1925457.html

However - and here's where I, uncaringly, put the cat among the pigeons...
I would simply plough on regardless.
Exit_MyFunction:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

This is the closing (tidyup) label of the procedure. You have to ask if you ever want to enter an error handler in such cases.

Cheers

 

[MarkK]

Access help for DAO.Recordset.Close and DAO.Recordset2.Close says ...

An alternative to the Close method is to set the value of an object variable to Nothing (Set dbsTemp = Nothing).

So it's probably sufficient to do this ...

Exit_MyFunction:
  Set rst = Nothing
  Set db = Nothing
  Exit Function

...which won't raise an error.

 

[vbaInet]

That's right lagbolt, it wouldn't raise an error. Some would argue that it's necessary to close the recordset (to avoid possible memory leaks), but for me it depends on the situation.

Here are two other ways if you really want to close the recordset:

Exit_MyFunction:
    if err.number <> 94 then
        rst.Close              
        Set rst = Nothing
        Set db = Nothing
    end if
    Exit Function

Exit_MyFunction:
    if not (rst is nothing) then
        rst.Close              
        Set rst = Nothing
        Set db = Nothing
    end if
    Exit Function

 

[boblarson]

Some would argue that it's necessary to close the recordset (to avoid possible memory leaks)

Interesting - since setting it to nothing removes it from memory

 

[vbaInet]

Interesting - since setting it to nothing removes it from memory

Some would argue that even though the reference (i.e. the pointer to memory) is broken, it still is in memory. Notice I said some, I'm indifferent

I bet there's been some arguments about this on here.

 

[MarkK]

The quote from the help file is not ambiguous. Obviously people can still argue.

 

[vbaInet]

It certainly isn't. But it is interesting that in the same help file some code examples include both rs.Close and set rs = nothing.

 

[Galaxiom]

I use the following style on ADO recordsets.
This is to close and remove it but it demonstrates the syntax.

If Not rs Is Nothing Then
If rs.State = adStateOpen Then: rs.Close
Set rs = Nothing
End If

(Note the colon in "Then:" is Syntactic Sugar. I include it to help draw attention to the fact I have used a single line IF, following the style of "Else:" in the single line Else.)

Regarding the failure to close and remove recordsets, I agree with lagbolt. Access help is unequivocal. I don't see the point of ignoring the advice when it is so easy to inclue the few lines, especially if encompassed as a function.

If you doubt its need when using a pointer to a connected recordset, perhaps consider what might be the case in a disconnected recordset where the whole thing exists in memory.

 

[LPurvis]

Not quite the can of worms it could have been - but unsurprising to see it echo on none the less.

I think a lot of what I have to say on the use of the Close method has already been said in this thread here, from a while back. So I'll try to avoid repeating it here.
There are occasions to not treat Help files as gospel - but Helpful. ;-) Everything was written by a person.
Early in its development cycle, DAO had some memory leak problems. It's widely believed they were fixed by MS, I'm certainly inclined to think so. I just don't feel the need to give myself completely to that belief.

If I can highlight some comments to make points about...

>> So it's probably sufficient to do this ...
My point here is, hopefully, self evident. ;-)

>> Interesting - since setting it to nothing removes it from memory
Absolutely (my linked thread post is relevant here). However, again in theory, so does allowing the procedure to end. VBA garbage collection would then remove all local variables from memory. So why do anything at all? To my mind, if you're going to do anything - then be thorough.

>> If you doubt its need when using a pointer to a connected recordset, perhaps consider what might be the case in a disconnected recordset
I'm not entirely sure of the nuance being raised here. Are you saying this is a case in point supporting thoroughly releasing and closing the object variables? (The separate function comment would seem to imply as much - i.e. the "you might as well" viewpoint.)

They're both (DAO connected and ADO disconnected) objects in memory, one of which happens to maintains a connection to the database still. Both still prone to any bugs or quirks the developers of the objects lovingly imbued them with.
For example, a shaped provider recordset accessing a Jet database will fail to disconnect the inherent connection - even when disconnected. Bug/necessity due to the provider - hard to say. But not what you'd expect.

Everyone can, and will continue to do either what they were taught/learned in their formative years - or what they've decided subsequently. For me, it's a question of harm/effect. Procedure cleanup is a nice, self documenting opportunity to say "OK, am I aware of the objects I've used in this procedure and are they all actually used and worthwhile - as I now dispose of them".

Cheers

 

[Galaxiom]

>> If you doubt its need when using a pointer to a connected recordset, perhaps consider what might be the case in a disconnected recordset
I'm not entirely sure of the nuance being raised here. Are you saying this is a case in point supporting thoroughly releasing and closing the object variables? (The separate function comment would seem to imply as much - i.e. the "you might as well" viewpoint.)

It has been said on this forum that the recordset is just a pointer and even if it did live on after the sub closed then the leak would be a very slow drip. Obviously a disconnected recordset is a whole lot more than a pointer so I my comment was meant to suggest a fire hose.

The trouble is all this stuff is in the shadows leaving it open to the likes of us to speculate on what goes on there. I am grateful for the input of those with better night vision than me but in the end it is still speculation to some extent.

I have seen some developers say that even setting the object to Nothing is superfluous. The lazy part of me says that the garbage collection should be OK but the conservative says to do what the programmers say.

One thing for sure is that if anything does go wrong it is better to cover my behind than try to justify why I didn't do it as recommended by Microsoft to some boss who accepts the word of an expensive consultant saying I am an idiot because I didn't do it right, even if it isn't what actually causes the problem.

You have gathered my drift with the function comment. A function with a recordset argument makes the cleanup a single command. It makes the lazy part of me happy without having to be frightened of that consultant.

 

[LPurvis]

Well all object variables are just pointers to the objects themselves in memory - created by the components of the library in question. Yes, a static ADO recordset (that's the biggest difference of data location) places a heavier client-side memory burden - but that is really the responsability of ADO to maintain. Not our VBA variable pointing at it.
Indeed it's that exact concept which makes me feel that the Close method is always important. Different programming environments handle their own variables (and pointers) differently. But the library in question (in this case ADO) should handle its own objects the same - releasing what is to be released when required (be that by an explicit call the to the Close method or just similar code called when the object is terminated... We can only hope that is the implementation... unless we are explicit ourselves, then that's all that can be done).

Until MS release the open source of all their libraries (which is half past never) then there will always be some speculation - even though we've have feedback from those on the inside in the past to help fuel the debate.

And as for these consultant types? Meh. They're just weird. And dress funny.

Cheers.

 

[Galaxiom]

Thanks Leigh. Logical and imformative as always.

The trouble with consultants is that management feels compelled to accept their advice because they are paid so much. To ignore their judgement would be to flag that the decision to engage them was wrong and a waste of money (which is often the case ).

Managers want to be seen to be responsible when out of their depth. Unfortunately they don't normally realise they are out of their depth when making the orignial decisions. Getting the consultant insulates them from the consequences of their ineptitude.

In Australia, the Board of Directors of one large bank gave themselves a huge bonus as reward for their wisdom of engaing a consultant to make a decision for them. I wish I could get a bonus for suggesting that someone else did my work. Unfortunatlely for ordinary folk we would only get retrenchment as a reward.

 

[The_Doc_Man]

My best advice was already given in an earlier post by LPurvis.

My preferred style is to "bracket" anything that gives you an error of that type by using On Error Resume Next to block all traps when you are doing a close of a recordset that could already be closed, then if appropriate, reassert the error trap with a new On Error GoTo Fubar. Along those lines, I am a belt AND suspenders person, so I also include the Set rsvar = Nothing after the rsvar.Close method is executed. OK, overkill. But if you are going to beat a dead horse, beat it hard. No sense in half-measures.

I also feel that this principle can legitimately be expanded to other actions dealing with closing of forms and reports, disposal of dynamically allocated memory, etc.

 

[Galaxiom]

My preferred style is to "bracket" anything that gives you an error of that type by using On Error Resume Next to block all traps

Well I guess On Error Resume Next is 100 percent reliable but I think I prefer:
If Not rs Is Nothing

I also feel that this principle can legitimately be expanded to other actions dealing with closing of forms and reports, disposal of dynamically allocated memory, etc.

Now why is there no UnDim to take those variables away?

 

[vbaInet]

Now why is there no UnDim to take those variables away?

Well, you can't really compare variables to objects in that respect. Variables are treated as first-class "objects" whilst, on the other hand, objects are not.

As you know a variable is a memory address whilst objects are a pointer to the interface of the dimensioned type. That is, they are not a pointer to a memory location, but just to the interface that handles the pointer to the memory address and its encapsulated methods and properties. Basically, the object simply inherits the methods and properties associated with that object type and interacts with them via the "hidden" interface. When you set an object to Nothing all you're doing is disassociating the object from the interface, however it still remains in memory.

Maybe this example will throw some more light:

Option Compare Database
Option Explicit


Private objCopy As Object

Public Function TestObjPointer()

    Call SetObjPointer
    
    Debug.Print "Copy rec count: " & objCopy.RecordCount & vbTab & "interface location: " & ObjPtr(objCopy)
End Function

Public Function SetObjPointer()
    Dim objMain As Object
    
    Set objMain = CurrentDb.OpenRecordset("Some Query")
    Set objCopy = objMain
    
    Debug.Print "Main after Set: " & ObjPtr(objMain) & vbTab & "Is Nothing? " & (objMain Is Nothing)

'    objMain.Close
    Set objMain = Nothing

    Debug.Print "Main after Nothing: " & ObjPtr(objMain) & vbTab & "Is Nothing? " & (objMain Is Nothing)
End Function

I've commented out objMain.Close for now. What I want you to do is:

1. run it when commented out and see the results
2. uncomment it and comment out Set ObjMain then run
3. uncomment both and run

... by calling TestObjPointer()

From the above you can make your own conclusions.

The help file mentions that Close is an alternative to Nothing but being an alternative doesn't mean they perform exactly the same function.

Variants are quite an interesting type of variable. It makes me wonder if it's actually a variable since it can "morph" into anything.

NB: ObjPtr() is a hidden function.

 

[PhoenixofMT]

Thank you all for the input. It turns out that my deadline for the project was shortly after I posted, but it looks like something was still broken when I sent it off so I'll get a chance to fix this part too. I think I'll go with the On Error Resume Next as a quick fix. I think I agree with LPurvis that I don't really want error trapping in my exit code any way.

Thanks again,

-- Phoenix

 

[Galaxiom]

What a fascinating demonstration, vbaInet.
That absolutely concludes any suggestion about Close and Nothing being the same.

Close clearly goes about an orderly routine while Nothing just drops the object. Obviously the Copy would be deleted when it was set to Nothing but who knows what else might be left lying around.

While some might continue to debate on recordsets I have no doubt that Close absolutely should be used for some objects.

In the case of a Connection object no doubt the Close would negotiate a shutdown of communication with the other object while Nothing would leave the connection hanging without the other end knowing, ultimately closing their end on timeout.

I will be sticking with Microsoft's advice and use both Close and Nothing on all objects.

 

[gemma-the-husky]

I think the use of resume next is one to consider judiciously. It depends what sort of error, doesn't it. You don't/ can't necessarily just plough on, can you.

What I don't understand is why raise an error

given the code

If Nz(some_field, "") then err.Raise 94, etc.
then why not just

If Nz(some_field, "") then
goto some label
end if
or is raising an error "slicker" in some way.

 

[Galaxiom]

What I don't understand is why raise an error

If you raise an error it will be dealt with as an error. Anything interacting with the procedure will know it is an error condition and deal with it as such.

If the procedure has an error handler it will take over as it would for any other error.

If there is no error handler in the procedure then the error will be passed back through the calling procedures until it finds a handler.

If you are logging errors it will be included with the other errors without having separate code.

Separate code would be necessary to deal with the pseudo-error because the execution cannot Go To the error handler without first raising an error because it won't like the Resume when it is encountered without being in an error condition.