[RESOLVED] "NEW" Recordset

I have defined my recordsets like this

Code:

---

Public Rs As New ADODB.Recordset

---

I Open them like this

Code:

---

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 -

Code:

---

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 ?

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

Code:

---

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 ....

Code:

---

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.

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

Code:

---

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.

Thanks - you have confirmed what I suspected

I believe, like you, this is the right way

Quote:

---

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

---

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

Code:

---

Set rs = Nothing

---

Thanks guys - thats all excellent


Makes a lot of sense

Quote:

---

Set rs = Nothing

---

Quote:

---

Originally Posted by 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

Code:

---

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

Code:

---

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

---