3

According to this answer, the moment a variable goes out of scope then the object's resources will be automatically released by VB. Question: is it okay if I used With...End when opening a DAO.Recordset since the resources is supposed to be cleaned up when the Sub goes out of scope, or should I still need to explicitly Close and set objects to Nothing?

For example,

Sub Test()
   With CurrentDb.OpenRecordset("SELECT * FROM Table1", dbOpenForwardOnly)
      IsThisAGoodPractice()
   End With
End Sub
Community
  • 1
  • 1
mdialogo
  • 463
  • 8
  • 15

1 Answers1

2

should I still need to explicitly Close and set objects to Nothing?

Inside the With block, you don't have an object variable which references the recordset so there is no way to set it = Nothing. After End With, the recordset is gone, so you can't set it = Nothing there either --- but it's gone already so that would be pointless.

Regarding its .Close method, I would call it same as for any object which includes a .Close method. But whether or not you call it, VBA will still dispose of the recordset at End With.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • @HandsUp additional clarifications please, someone told me that it is not good practice to use With...End when opening DAO recordsets and I should still declare the object variables and manually dispose it. For me, using With..End is much simpler. Am I wrong? – mdialogo Nov 10 '15 at 05:32
  • I don't see how `With CurrentDb.OpenRecordset(...` is all that much simpler than `Set rs = CurrentDb.OpenRecordset(...` Is your savings only avoiding `Dim rs As DAO.Recordset`? – HansUp Nov 10 '15 at 05:40
  • Yes, those variable declarations and manually disposing them. Or maybe this is just a matter of preference? Anyway, thanks for you answer! – mdialogo Nov 10 '15 at 05:54