20

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?

Curtis Inderwiesche
  • 4,792
  • 18
  • 55
  • 81
  • If you use a `With OpenRecordset ... End With` pattern then the object is temporary. No need to manually close it or set it to nothing anymore. This allows the scope to be very clearly defined. – HackSlash Apr 13 '20 at 19:30

4 Answers4

16

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.

Jojo Sardez
  • 7,872
  • 3
  • 24
  • 38
  • 1
    So, does doing one bypass the need for doing the other? – Curtis Inderwiesche Mar 30 '10 at 02:39
  • Not really although you can bypass setting the recordset to nothing and will not encounter any error. Its just that it is the best practice to set the recorset to nothing after you closed it especially when you have no use to that recordset or you will not going to access the same recordset again. – Jojo Sardez Mar 31 '10 at 00:35
  • 4
    Your answer refers to database connections, but the question used a recordset. Database variables are different from others in that what you can safely do with them depends on how they were initialized (CurrentDB vs. DBEngine(0)(0)). With a recordset variable, closing the recordset does not close the database connection at all. – David-W-Fenton Apr 02 '10 at 17:19
14

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.

Francesco B.
  • 2,243
  • 3
  • 22
  • 29
David-W-Fenton
  • 22,262
  • 3
  • 41
  • 55
7

You can set Recordset to Nothing without needing to call Close, according to official documentation:

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

More info: Recordset.Close Method (DAO)

jurev
  • 903
  • 1
  • 9
  • 16
1

Well, in my own experience, if the recorset object(hereinafter referred to as "RS") is declared locally(within the function/procedure, hereinafter referred to as "B") and will not be delivered out to where B is called(hereinafter referred to as "A"), it's safe and suggested to close and set RS to nothing inside B; but in the following situations:

  1. RS is delivered as one of the parmeter argument of B(either byval or byref) from A
  2. RS is declared in B and is to be one of the return value(s) of B for A to use

RS in B should only set to nothing without closing it, orelse the recordset object returned to A(or sent to B as one of the parameter(s) from A) will also be closed and set nothing, making it inaccessible in A, even if you returned RS to A in advance and then close in B!

Elletlar
  • 2,819
  • 7
  • 27
  • 33
Edward
  • 33
  • 5