I guess I should stress that the whole point of Closing and setting objects to Nothing is simply to free up the system resources consumed by that object. Calling the Close method on a Recordset object will release the recordset's resources. Letting the object go out of scope should do the same thing. Explicitly setting the object to Nothing should do the same thing. Setting the object to Nothing should implicitly close the object.

But as my example with the Dim rst As New ADODB.Recordset syntax demonstrates, what should happen doesn't always happen. So I just like to do everything explicitly: instantiate the recordset object correctly, then when I'm done with it, call the Close method, then Set the object to Nothing. Seems like a good practice to me.

Regards,

Bob

Thanks a lot for that, Bob!
I had been using the new keyword, and believed that they get terminated after closing them, thanks again!

Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW

Hey Bob! Just to make sure, for connections and commands in ADO, I should always follow the idea of instantiating the objects with set obj = new.....
and then when im done, I just set obj = nothing, right???


Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW

Hi Sam,

Yes, you should take these steps with every COM object you'll ever run into, including ADO objects and those you create yourself using class modules.

Command objects can be destroyed immediately after executing them. Exactly when to close/destroy Connection objects is a bit debateable. Using a global connection object is the easiest way to go, question is: 1. Do you hold a single connection open for an entire session? or 2. Do you open and close the connection as needed? If your licensing scheme will let you keep a connection open for all of your users, thats probably the way to go in a two-tier setup (I can't remeber if you're using SQL Server or not.) Opening and closing connections from Access can get expensive. If you go with holding a single connection open, just add something like the following where ever it is that users finally exit the app:

Private Sub Form_Unload(Cancel As Integer)

    If MsgBox( _
      "Do you want to exit the application?", _
      vbQuestion + vbYesNo, _
      "Quit now?") = vbYes Then
          ' Release the global connection object
          If Not gcnn Is Nothing Then
              If gcnn.State = adStateOpen Then gcnn.Close
              Set gcnn = Nothing
          End If
          ' Exit the app
          DoCmd.Quit
    End If

End Sub

If you want to open and close connections as needed, you can set up two global variables in a standard module like:

Public gcnn As ADODB.Connection
Public gcnnstring As String

then initialize the connection string (but not open the connection yet) behind your login form, like:

Private Sub cmdLogin_Click()

    gcnnstring = "provider=SQLOLEDB.1" & _
                 "; data source=" & txtServer & _
                 "; initial catalog=" & txtDatabase & _
                 "; user id=" & txtUserName & _
                 "; pwd=" & txtPassword & _
                 "; packet size=4096;Connection Lifetime=1"
    Set gcnn = New ADODB.Connection
    gcnn.ConnectionString = gcnnstring

End Sub

then open/close the connection behind your data entry forms when needed, like:

Private Sub cmdLoadData_Click()

    Dim rst As ADODB.Recordset

    If gcnn.State = adStateClosed Then gcnn.Open

    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM Customers", gcnn, adOpenKeset, adLockOptimistic

    ' do processing

    gcnn.Close

End Sub

Finally, since your getting up and running with ADO, you should have the following life-saving one-liner in your tool kit. The following dumps the entire contents of a recordset object to the debug window:

Debug.Print rst.GetString(adClipString, , ";")

This is a really handy way to see what your recordset objects actually contain without having to iterate through the fields collection. Just bear in mind that after running this, the recordsets end of file (EOF) condition will be True, so you will have to reopen the recordset to manipulate its contents.

HTH,

Bob

Hi Sam:

I figured out what is actually going on with the "As New" syntax and setting objects instantiated in this way to "Nothing". It isn't the case that the object is not destroyed when you Set is to Nothing. It actually is. What's happening is that, unlike an explicitly instantiated object (Set rst = New ADODB.Recordset), an implicitly instantiated object isn't created until you use it (that is, until VBA encounters it in your code for the first time). The problem with this behavior is that you simply can't compare an implicitly instantiated object to Nothing. When you try, VBA thinks you are telling it that you want to use the object, so it goes ahead and creates a new one. Hence,

' implicit instantiation
Dim rst2 As New ADODB.Recordset
rst2.Close
Set rst2 = Nothing
If rst2 Is Nothing Then

Its on this last line that things get goofy. Before rst2 is compared to Nothing, VBA actually creates a new instance of rst2. So 'If rst2 Is Nothing Then' will never actually evaluate to True. You can see this by running the code I posted earlier, and watching what happens to the value of the recordset objects in the locals window. I've commented the code below pretty extensively to give you the blow-by-blow commentary:

Sub rstTest()

    ' rst1 is declared
    Dim rst1 As ADODB.Recordset

    ' rst2 is implicitly instantiated but not created until
    ' its properties or methods are called latter on
    Dim rst2 As New ADODB.Recordset

    Dim strSQL As String
    strSQL = "SELECT * FROM Customers"

    ' rst1 is explicitly instantiated AND created immediately
    Set rst1 = New ADODB.Recordset

    ' rst1 already exists
    rst1.Open strSQL, CurrentProject.Connection, adOpenStatic

    ' rst 2 is created for the first time when its Open method is called
    rst2.Open strSQL, CurrentProject.Connection, adOpenStatic

    ' rst1 is closed but not destroyed
    rst1.Close

    ' rst1 is destroyed
    Set rst1 = Nothing

    ' rst2 is closed but not destroyed
    rst2.Close

    ' rst2 is destroyed
    Set rst2 = Nothing

    ' rst1 is compared to Nothing.
    ' The comparison evaluates to True
    If rst1 Is Nothing Then
        ' This line of code executes
        MsgBox "Explicit rst1 is nothing."
    Else
        MsgBox "Explicit rst1 is NOT nothing"
    End If

    ' rst2 is RECREATED here before the comparison to
    ' Nothing occurs because, since it was destroyed earlier,
    ' VBA thinks (mistakenly) that it is ecountering rst2
    ' for the first time
    ' The comparison evaluates to False
    If rst2 Is Nothing Then
        MsgBox "Implicit rst2 is nothing."
    Else
        ' This line of code executes
        MsgBox "Implicit rst2 is NOT nothing"
    End If

End Sub

Even though VBA ought not re-create implicitly instantiated rst2 in this way, it does anyway! So the moral is: if you use "As New" syntax, it may not be clear where and when VBA instantiates your objects.

Regards,

Bob

... And that's probably the best argument there is against using the "As New" syntax. Always create and destroy your objects explicitly, so you always know exactly what's happening when, rather than relying on (or being hoist by) VB doing it for you in unexpected ways, like this.

Why save a teeny bit of typing time when the cost may be hours of pulling your hair out trying to understand why perfectly reasonable-looking code isn't working ...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Then, after all the head scratching, along comes VB.NET and, wouldn't you know it, "As New" is back in vogue. The glitch with "As New" in VB/VBA is that the compiler creates code that checks implicitly instantiated (or, "auto-instancing") variables before EVERY reference to them (even if you're comparing them to Nothing), and automatically kicks out a new instance if the object variable is currently Nothing. The corollary, of course, is that if a variable is never referenced the object is never created.

Not so with VB.NET. VB.NET doesn't support a syntax that buys you VB6's auto-instancing behavior. "As New" is just one of the many forms that .NET variable initializers can take, and in fact (heavy sigh), its the PREFERRED form! Sticking with ADO.NET for example, in a statement like:

Dim ds As New System.Data.DataSet

an object is always created when the Dim statement executes. So:

Dim ds As System.Data.DataSet
ds = New System.Data.Dataset

and

Dim ds As System.Data.Dataset = New System.Data.Dataset

and

Dim ds As New System.Data.DataSet

are all functionally equivalent (the latter being preferred simply 'cause its the most concise.) Since no auto-instancing occurs, comparison to Nothing always evaluates to True (before and after garbage collection) if the object variable is Nothing, as in the following:

Module Module1

    Sub Main()

        ' ds is created when its declared using As New
        Dim ds As New System.Data.DataSet

        ' make ds eligible for garbage collection
        ds = Nothing

        ' evaluates to True
        If ds Is Nothing Then
            MsgBox("ds is nothing")
        End If

        ' force garbage collection and deallocate
        ' ds's memory block on the managed heap
        GC.Collect()

        ' evaluates to True
        If ds Is Nothing Then
            MsgBox("ds is nothing")
        End If

    End Sub

End Module

Just when you think you got something figured out...

Bob

Hey Bob,
I have a small problem, which I think will be really easy for you.
As you know I'm converting the DAO to ADO, so in this form I have in DAO a querydef that was executes a query from access, and then it is put into a recordset. The recordset was then used for a rslicno.recordcount in an if statement:

Private Sub Lic_No_AfterUpdate()

Dim db As Database
Dim rslicnoq As QueryDef
Dim rslicno As Recordset

Set db = DBEngine.Workspaces(0).Databases(0)

If IsNull(Me.Lic_No) Then
    MsgBox "Licence Number was left empty." & vbNewLine & "Select View or Add button then enter a licence number", vbOKOnly + vbCritical, "Licence Number"
    Exit Sub
Else
    If Len(Me.Lic_No) <> 4 Then
        MsgBox "Length of Licence Number must be 4 digits", vbOKOnly + vbCritical, "Licence Number"
    Else
        Set rslicnoq = db.QueryDefs("qry-FindLabLicNo")
            rslicnoq![Licno] = Me.Lic_No
        Set rslicno = rslicnoq.OpenRecordset
        If Me.Lic_Choice = 1 Then 'View Licence Number
            If rslicno.RecordCount < 1 Then
                MsgBox "Licence Number entered does not exist" & vbNewLine & "To ADD a new licence, select Add New Licence", vbOKOnly + vbCritical, "Licence Number"
                Me.Lic_Choice.SetFocus
            End If
        ElseIf Me.Lic_Choice = 2 Then 'Add Licence Number
            If rslicno.RecordCount > 1 Then
                MsgBox "CAUTION: Licence Number entered currently exists" & vbNewLine & "To VIEW licence, select View Licence", vbOKOnly + vbCritical, "Licence Number"
                Me.Lic_Choice.SetFocus
            End If
        End If
        If Left(Me.Lic_No, 1) <> "3" Then
            Me.Class___Test.Enabled = True
        Else
            Me.Class___Test.Enabled = False
        End If
        Me.Laboratory_Organization.SetFocus
    End If
End If

End Sub

I tried to get rid of the querydef and just use one recordset but then since the DAO code refers to a query in the database it gives me problems. I tried using a command object and its commandtext string to execyte the query but then it also gives me errors. I tried bunch of other stuff, everyone gave me different errors. If you got any ideas, I'm all ears, thanks a lot! if you need more info let me know


Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW

Hi Sam,

First, never use the "e" word. Its a jinx, as you'll see in a second. It appears to me that you are using a parameterized stored query to search for your license numbers. That's relatively easy to do with DAO, but ADO doesn't work with parameterized stored queries quite as easily; they need to be accessed through the Command property of a Procedure object using ADOX, so that the parameters collection of the Command object gets populated correctly. So first, set a reference in your db to the ADOX library (Microsoft ADO Ext. 2.x for DDl and Security). ADO also complicates the process of testing for an empty recordset, in that many ADO recordsets don't support the recordcount property (forward-only is one example). To test for ADO recordset boundaries you typically use the BOF and EOF properties, which is what I do below.

I'm curious about the following:

Set rslicnoq = db.QueryDefs("qry-FindLabLicNo")
rslicnoq![Licno] = Me.Lic_No
Set rslicno = rslicnoq.OpenRecordset

this doesn't actually execute in your DAO module, does it? It doesn't seem like it would, given that you make the query field assignment prior to opening the recordset. To set stored query parameters in DAO, you'd typically use something like the following. "[Forms]![Form1]![Lic_No]" would be already set up in the criteria field of your stored query.

Private Sub Lic_No_AfterUpdate()
    Dim db As DAO.Database
    Dim rslicnoq As DAO.QueryDef
    Dim rslicno As DAO.Recordset

    Set db = CurrentDb

    Set rslicnoq = db.QueryDefs("qry_FindLabLicNo")
    rslicnoq.Parameters("[Forms]![Form1]![Lic_No]") = Me.Lic_No.Text
    Set rslicno = rslicnoq.OpenRecordset(dbOpenForwardOnly, dbReadOnly)

     If Me.Lic_Choice = 1 Then
         If rslicno.RecordCount < 1 Then
            MsgBox "Licence Number entered does not exist" & _
                        vbNewLine & "To" & _
                        "ADD a new licence, select Add New Licence", _
                         vbOKOnly + vbCritical, "Licence Number """
            End If
        End If

    rslicno.Close
    Set rslicno = Nothing
    Set rslicnoq = Nothing
    Set db = Nothing

End Sub

Notice that the DAO recordset supports a recordcount property here. The ADO equivalent doesn't, so you need to evaluate BOF and EOF instead:

Private Sub Lic_No_AfterUpdate()

    Dim rslicnoq As ADODB.Command
    Dim rslicno As ADODB.Recordset
    Dim prm As ADODB.Parameter
    Dim cat As ADOX.Catalog
    Dim prc As ADOX.Procedure

   Set cat = New ADOX.Catalog

    cat.ActiveConnection = CurrentProject.Connection
    Set prc = cat.Procedures("qry_FindLabLicNo")

    Set rslicnoq = prc.Command

    For Each prm In rslicnoq.Parameters
        prm.Value = Eval(prm.Name)
    Next prm

    Set rslicno = rslicnoq.Execute

    If Me.Lic_Choice = 1 Then
        If rslicno.BOF And rslicno.EOF Then
            MsgBox "Licence Number entered does not exist" & _
                        vbNewLine & "To" & _
                        "ADD a new licence, select Add New Licence", _
                        vbOKOnly + vbCritical, "Licence Number """
        End If
    End If

    rslicno.Close
    Set rslicnoq = Nothing
    Set rslicno = Nothing
    Set cat = Nothing
    Set prc = Nothing

End Sub

If you use this type of approach in ADO, you'll need to rewrite your query also so that your parameterized fields are explicitly indicated:

PARAMETERS [Forms]![Form1]![Lic_No] Text ( 255 );
SELECT tblLicenseNumbers.ID, tblLicenseNumbers.LicNo
FROM tblLicenseNumbers
WHERE (((tblLicenseNumbers.LicNo)=[Forms]![Form1]![Lic_No]));

Another route you might consider taking, that wouldn't involve hastling with stored parameter values, is simply to use the ADO Find method, and search whichever object stores your license number, as in:

Private Sub Lic_No_AfterUpdate()

    Dim cnn As ADODB.Connection
    Dim rslicno As ADODB.Recordset
    Dim strSQL As String
    Dim strCriteria As String

    Set cnn = CurrentProject.Connection

    strSQL = "SELECT LicNo FROM tblLicenseNumbers"
    Set rslicno = New ADODB.Recordset
    rslicno.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

    strCriteria = "[LicNo] = '" & Me.Lic_No.Text & "'"

    If Me.Lic_Choice = 1 Then
        If Not rslicno.BOF And Not rslicno.EOF Then
            rslicno.MoveFirst
            rslicno.Find strCriteria, , adSearchForward
            If rslicno.EOF Then
                MsgBox "Licence Number entered does not exist" & _
                            vbNewLine & "To" & _
                            "ADD a new licence, select Add New Licence", _
                            vbOKOnly + vbCritical, "Licence Number """
            End If
        End If
    End If

    rslicno.Close
    Set rslicno = Nothing
    Set cnn = Nothing

End Sub

Hopefully one or the other of these methods will work for you.

Regards,

Bob

Just noticed you'd actually want:

PARAMETERS [Forms]![Form1]![Lic_No] Text (4);
SELECT tblLicenseNumbers.ID, tblLicenseNumbers.LicNo
FROM tblLicenseNumbers
WHERE (((tblLicenseNumbers.LicNo)=[Forms]![Form1]![Lic_No]));

since your license ID can only be 4 characters long...