[RESOLVED] "NEW" Recordset

[George1111]

I have defined my recordsets like this

Public Rs As New ADODB.Recordset

I Open them like this

Set Rs = New ADODB.Recordset
Rs.Open StrSql, Db, adOpenStatic, adLockOptimistic

It seems to me to be a DOUBLE "NEW"

Should it be the following -

Public Rs As ADODB.Recordset
.......
Set Rs = New ADODB.Recordset
Rs.Open StrSql, Db, adOpenStatic, adLockOptimistic

What happens when I say "NEW" ?

What is the "CORRECT" way to handle ADO Records sets ?

[DataMiser]

I think in the case of a public RS you should use the

Public RS as ADODB.Recordset
........
Set RS = New ADODB.Recordset

In the case of a Local RS I you could do it this way as well or you could also do it ....

Dim RS as New ADODB.Recordset
RS.Open ......

My Understanding is that New creates an object and/or allocates memory for it. If you Dim it as new then Set as new it could be wasting that memory allocated. It is possible even likely that the Set uses the same memory so no memory is lost but I wold think that best case senario would be that the program is slightly slower with the extra call to New which is not needed.

[Shuja Ali]

Actually it is not something to with Public & local. Why initialize it before you are actually using it. I always prefer this style because it makes code much more understandable

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

This means I will initialize it only when I need it. Now some may argue that there is no difference other than that. Imagine if you were supposed to test the object = nothing, using Dim As new would always return a false because the object is already initialized.

There was this nice thread here which discussed about implications of using Dim As New, but i was not able to find it.

[George1111]

Thanks - you have confirmed what I suspected

I believe, like you, this is the right way

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

[dglienna]

Everytime you do that, you HAVE to destroy it after closing. Add this:

Set rs = Nothing

[George1111]

Thanks guys - thats all excellent


Makes a lot of sense

Set rs = Nothing

[dee-u]

Actually it is not something to with Public & local. Why initialize it before you are actually using it. I always prefer this style because it makes code much more understandable

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

This means I will initialize it only when I need it. Now some may argue that there is no difference other than that. Imagine if you were supposed to test the object = nothing, using Dim As new would always return a false because the object is already initialized.

There was this nice thread here which discussed about implications of using Dim As New, but i was not able to find it.

Just to illustrate

Option Explicit

Private Sub Form_Load()
    LooseBinding
    StrongBinding
End Sub

Private Sub LooseBinding()
    Dim adoRS As New ADODB.Recordset
    Set adoRS = Nothing
    MsgBox adoRS Is Nothing ' returns false
End Sub

Private Sub StrongBinding()
    Dim adoRS As ADODB.Recordset
    Set adoRS = Nothing
    MsgBox adoRS Is Nothing ' returns true
End Sub