"rs.close" and/or "Set rs = Nothing"

Question

Best practice question:

Q: At the end of my Subs, should I BOTH close the open Recordset & then "Set rs = Nothing".

Or, is "Set rs = Nothing" alone sufficient? (I have a mixed bag, currently)

Private Sub btnSave_Click()

    Dim db As Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Desc_Mfg_Norm", dbOpenTable)

    'do stuff

Exit:

    rs.Close
    Set rs = Nothing
    Set db = Nothing

exit sub

First it is `rs.close()`. Second do both as one closes the object and other releases it from memory. — Parfait, Dec 29 '17 at 01:52
`Set EVERYTHING = Nothing` is done automatically at every `End Sub` and `End Function` so is voodoo programming. Here's info on VBScript (which copies VBA). — ACatInLove, Dec 29 '17 at 02:34
@ACatInLove, are you suggesting NEITHER "rs.close" or "set rs = nothing" is explicitely required? — Mark Pelletier, Dec 29 '17 at 02:36
No, just setting an object to nothing just before an end sub/function is voodoo programming and does nothing. It's origins are actually with a very old buggy version of ADO. If you follow the links in the link I gave you'll find that info. Cut and paste programming perpetuates voodoo practises. — ACatInLove, Dec 29 '17 at 02:41
Forgot to Ctrl + V http://stackoverflow.com/questions/39215460/declaring-variables-memory-leaks — ACatInLove, Dec 29 '17 at 02:47

score 3 · Answer 1 · answered Dec 29 '17 at 09:44

The answer is that neither is needed. They are left-overs from Access Basic, the predecessor of VBA of Access 1.x and 2.0.

In VBA, the garbage collector will clean them for you.

But many - often including me - still use them to signal that you don't (intend to) use the object later in the function - indeed the Close method.

Mind you though, that third-party objects - like ActiveX components and, say, Excel - usually must be closed and killed. This also speaks for using the commands - to make it a habit when coding.

score 1 · Accepted Answer · answered Dec 29 '17 at 01:54

1

People tend to call what you are doing as "best practice", but you can bypass the close method respecting all best practices. From official documentation:

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

Source: https://msdn.microsoft.com/en-us/library/office/ff836011.aspx

answered Dec 29 '17 at 01:54

statosdotcom

3,109
2
17
40

I am still researching. General consensus seems to indicate that neither “rs.close” or “Set rs = Nothing” may be needed as garbage collection and variable release may be handled when they go out of scope at the end of the sub (see links posted above). I'll flag the correct answer shortly. – Mark Pelletier Dec 29 '17 at 04:45
Mark, I beg your pardon if I am bothering you about details. What I would like to point is (i.e., if I am right on my thoughts): shall we suppose you have asked "how many spoons of coffee powder I need to provide in order to make my drink with 200ml of water?". For this I answered to you: "Two full soup spoons". Then, at the middle of the process you discover that you like sweet coffee. There you are, with all my respect, "still researching" how much sugar you will need to make your good drink, but the original question was: "how many spoons of coffee". – statosdotcom Dec 29 '17 at 13:48
Of course you have no obligation at all, but, in order to respect the order of things you have put from the beginning of your matters, may be the case of editing the original question or accept my answer and open another one to clear how much of sugar. I would like to remember, additionally: this platform, stackoverflow, and all it's functionalities, is entirely free. Thank you and best regards. – statosdotcom Dec 29 '17 at 13:48

score 0 · Answer 3 · answered Sep 07 '19 at 18:13

I go with the solution of
rs.Close.
If Not rs is Nothing then Set rs= Nothing.

Keep in mind that a closed Recordset might reopen ( I use it in some occasions). Also is a good practice to close everything you open...the garbage collector might do the job...might not....

"rs.close" and/or "Set rs = Nothing"

3 Answers3