New.ADODB Recorset - General point

[Bruce Fox]

I have noticed recently the incorect setting of Data Types that wont be released (Set to Nothing).

If you are referencing the AX ADO Lib (or DAO) the following should apply:

'Eg.
Dim rst as ADODB.Recordset
 
  Set rst = [b]New[/b] ADODB.Recordset
 
 
  'Code here
  '
  '
 
 
  Set rst = Nothing

This example below by Armbruster shows why:

In my example the dim statement declares an object variable of type adodb.recordset. Because I did not use the NEW keyword in the dim statement, it did not create the object from the adodb.recordset class. The set statement then creates the object from the underlying class.

So what is the difference with my example and yours? The difference has to do with how COM implements the underlying class.

There was a lengthy discussion on the board quite some time ago. Basically, your example will not totally release the memory after the object is destroyed because of the way the class is implemented by dim as a new object in the same statement.

Run this code . . .

Private Sub Command1_Click()
    Dim ado1 As New ADODB.Recordset
    Dim ado2 As ADODB.Recordset
    Set ado2 = New ADODB.Recordset
    Set ado1 = Nothing
    Set ado2 = Nothing
    If ado1 Is Nothing Then
        MsgBox "ado1 properly destroyed"
    Else
        MsgBox "ado1 is still around"
    End If
    If ado2 Is Nothing Then
        MsgBox "ado2 properly destroyed"
    Else
        MsgBox "ado2 is still around"
    End If
End Sub

Bruce.