Set Recordset = Nothing

[kirkm]

Normally I'd open a recordset, do stuff then Set it to = Nothing in the same procedure.

However if I wanted to pass the recordset ByRef to a function, can I Set it to = Nothing in the function?

Or is none of this important and you don't really need to Set it to = Nothing at all?

 

[June7]

VBA should automatically do cleanup when procedure ends but some consider it good practice to explicitly do it.

Don't think I've ever passed a recordset object. I have passed an array and also set a public recordset object variable.

 

[MajP]

There are very few times when you have to close a recordset and very, very, very few times to set any object to nothing. If it makes you sleep better you can close the recordset in the other procedure. But if you do nothing it will close when it goes out of scope.

 

[theDBguy]

Hi. What does your own testing tells you. I try to close/destroy any objects I created, just to be safe; but like others have said, Access is supposed to do it for you anyway. Recordsets are naturally passed ByRef, I believe, but maybe Set rs = Nothing is automatically ignored if the rs object is still in use. Either that or the local variable is cleared, but the actual object is not yet destroyed until the other process using it is done.

 

[kirkm]

So really I don't need to set it to nothing ?
OK
Thanks..

 

[Galaxiom]

I have often passed recordsets as the return value of functions after creating them as another variable inside the function.

IIRC once it is assigned to the return value, the original variable can be set to Nothing but it cannot be closed since it is still pointing to the same object.

 

[The_Doc_Man]

There is a matter of style to consider. Where it is feasible, you should always close objects (when done with them) in the same scope from which they were opened. This would be an issue of "spaghetti code" if you did otherwise. However, having a common set of OpenFile, CloseFile routines doesn't violate that rule if the OpenFile and CloseFile routines are invoked from the same level.

It is not that you cannot do this object opening and closing as part of your code anywhere that they seem to make sense. However, by performing opening and closing actions closer to each other, you minimize the opportunity to forget an object variable and thus leave it "dangling."