Set Recordset = Nothing (1 Viewer)

kirkm

Registered User.
Local time
Today, 14:56
Joined
Oct 30, 2008
Messages
1,257
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

AWF VIP
Local time
Yesterday, 17:56
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:56
Joined
May 21, 2018
Messages
8,463
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

I’m here to help
Staff member
Local time
Yesterday, 18:56
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 14:56
Joined
Oct 30, 2008
Messages
1,257
So really I don't need to set it to nothing ?
OK
Thanks..
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:56
Joined
Jan 20, 2009
Messages
12,849
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

Immoderate Moderator
Staff member
Local time
Yesterday, 20:56
Joined
Feb 28, 2001
Messages
27,001
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."
 

Users who are viewing this thread

Top Bottom