1

In VBA, is it good practice to set an object to Nothing before declaring a new instance of it?

For example:

Private Sub SetupConn()

Const Provider As String = "sqloledb"
Const LanDataSource As String = "127.0.0.1"
Const WanDataSource As String = "mail.12345678.biz:12345"

Dim UserName As String
Dim Password As String

UserName = "myusername"
Password = "mypassword"

Here is where I wonder if I should close the connection or set it to nothing before reopening to a new one.

Set mDBconn = New ADODB.Connection

Select Case mConnMethod
Case WAN
    mConnStr = "Provider='" & Provider & ";Data Source=" & WanDataSource _
             & ";User ID=" & UserName & ";Password=" & Password
Case Lan
    mConnStr = "Provider='" & Provider & ";Data Source=" & LanDataSource _
             & ";User ID=" & UserName & ";Password=" & Password
End Select
End Sub

The scenario is that a user is on the road, using a mobile data connection, he arrives at the office and wants to go straight to a high speed wifi connection without closing and reopening the program. In this case what I would do is set the connection method to LAN and call this sub with a reset parameter.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Freeman Helmuth
  • 173
  • 1
  • 16
  • Not sure in VBA, but if you set the variable with a new reference, the old one will be overwritten and caught by the garbage collector since not used any longer. – Matteo NNZ Apr 23 '15 at 18:08
  • That's part of the reason I tagged it ADODB, I didn't know if there was any harm to be had by setting the object to nothing without closing it. – Freeman Helmuth Apr 23 '15 at 18:09
  • Then, better wait for someone who knows better the adodb model. I know the above, but I'm not sure so don't want to spread wrong information around. Take it with care for the moment. – Matteo NNZ Apr 23 '15 at 18:10
  • Possible duplicate of [Is there a need to set Objects to Nothing inside VBA Functions](http://stackoverflow.com/questions/517006/is-there-a-need-to-set-objects-to-nothing-inside-vba-functions). – Comintern Apr 24 '15 at 04:42
  • I guess part of my question is concerning how this relates to closing an ADODB connection. – Freeman Helmuth Apr 24 '15 at 11:35
  • I would still be curious what the answer to this question is, but I actually don't need this scenario anymore. – Freeman Helmuth May 01 '15 at 02:51

1 Answers1

1

I guess according to this Whats the difference between rs.close vs rs = nothing in a RecordSet, It is good to both close AND set nothing. This answers my question.

Community
  • 1
  • 1
Freeman Helmuth
  • 173
  • 1
  • 16