Set vs Dim for adodb objects?

[Dan01]

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

Which is the preferred way to create a new ADODB object? Thanks, Dan.

 

[drwunz]

Both, actually. As I understand it, the Dim defines the variable and typecasts it. Set sets it to 'point' to a specific occurrance of the object type.
Now, using 'new' in the Dim statement may take the place of a Dim and Set together...
If I'm wrong, someone please let me know...

 

[Dan01]

I did notice if using
Dim rs As New ADODB.RecordSet
if I type rs., in the vb module, a selection box will appear with a list of methods and properties such as Close, Open. This helpful selection box does not appear, however, when using Set rs = New ADODB.RecordSet. Dan.

 

[lisfolks]

Here's how I normally code it:

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

Using

Dim rsImage As New ADODB.Recordset

may do both in one line, but I don't know for sure, either.

It definitely works with the two separate lines: one for the 'Dim' (notice, there's no 'New' in that line) to allocate the space, and a second line for the 'Set' (that's where the 'New' goes) to instantiate the object.

Also, make sure you do

Set rsImage = Nothing

when you're done with the recordset. This deallocates the memory space and prevents various problems.

 

[FRoeCassNet]

When you do the Dim obj As ADODB.Recordset, Visual Basic will create the pointer that can reference an object of type ADODB.Recordset.

The Set command will set a reference to an existing object or with the New keyword it will create an whole new instance of the object. Setting a reference to nothing will release the reference you have (Set obj = Nothing).

Creating the instance is a time consuming process and for performance reasons you may wish to create a public object adn then throughout the code set/release references to that object as needed. The thing to remember is that once an object is created it will stay around forever until the all the references have been released (or the program ends)

So, if you have one procedure that looks up an employee and another that looks up that employee's home department. You can define your public variable (Dim rsGlobal As ADODB.Recordset), create it in an initialization routine (Set rs = New ADODB.Recordset). And then use it throughout the code.

Here's some samples, it doesn't make a lot of "real world" sense, but it'll give an idea of how to create an object once and then reuse it.


'GLobal space
Dim rsGlobal As ADODB.Recordset

'In some initialization routine (create it)
Set rsGlobal As New ADODB.Recordset

'In some termination routing (Destroy it)
Set rsGlobal = Nothing

Function GetEmpID (ByVal sName as String) As Long
Dim rsEmp as ADODB.Recordset
Set rsEmp = rsGlobal
rsEmp.Open("SELECT ID FROM Emps WHERE Name='" & sName "'&quot
GetEmpID = rsEmp("ID&quot
rsEmp.Close
set rsEmp = Nothing
End Function

Function GetHomeDept (ByVal nID As Long) As String
Dim rsDept as ADODB.Recordset
Set rsDept = rsGlobal
rsDept .Open("SELECT Dept FROM Depts WHERE EmpID=" & nID)
GetHomeDept = rsDept ("Dept&quot
rsDept .Close
set rsDept = Nothing
End Function