Whats the difference between rs.close vs rs = nothing in a RecordSet

Question

I often find it confusing as to when it is appropriate to use:

rs.Close 

opposed to

Set rs = Nothing

I can understand needing to close a connection to a source, but should I be using both when the variable falls out of scope?

Can I just set the variable to Nothing in order to skip the step of Closing the connection? Would this be considered a bad practice?

Answer 1

By using the "Close" method you are closing the connection to the database but is still in the memory where you can open again using the "Open" method.

Setting the recordset to "Nothing" on the other hand releases the object completely from the memory.

Answer 2

The Close method tears down the memory structure.

Setting the variable to Nothing clears the pointer to that memory structure.

Theoretically, clearing the pointer should release the memory the pointer was referring to, because VBA uses reference counting for determining when it can release memory. Unfortunately, various things can go wrong and the reference count can end up out of whack, and memory won't be released even when it should be.

Thus, to be sure you're not subject to memory leaks, or the weird kinds of bugs caused by implicit and unreleased references, you both Close and set to Nothing.