Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Question Unanswered: Quick ADO recordset question

    I'm new at this (I've said this before, haha) and right now I'm coding in 'whatever works' mode. However, I'm curious about something. I've got two different pieces of code that seem to behave in much the same way but I don't really know what the difference is between them performance-wise or anything else-wise.

    Both pieces of code seem to open a recordset, assign a source, execute that source query against the SQL server, and assign the results back to the recordset. I'm a total ADO dope, so I may not even be telling the story right.

    strSQL in both examples is a SQL query passed to the sub.

    Code A:
    Code:
    dim rst as ADODB.Recordset
    Set rst = cnConn.Execute(strSQL)
    Code B:
    Code:
    dim rst as ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnConn, adOpenForwardOnly
    cnConn is an ADODB.Connection object.

    What's the difference between them? Are they doing the exact same thing, or slightly different things? Which one is better suited to which jobs?

    Thanks in advance.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    THey are both doing much the same thing. The first instance allows the connection.execute method to implicitly instanciate the object.

    I prefer the second approach.

    There is a third approach, where you implicitly instanciate the objects in the declaration.

    Code:
    dim rst as New ADODB.Recordset
    WHen an object is declared implicitly, its creation and release are handled automatically by VB at the same level of scope as where they are declared, so you should NOT release the objects yourself.

    While this has the effect of simplifying the source code, it has another side effect - the object code of an implicitly instanciated object runs a bit slower than does the object code of an explicitly instanciated object. In every location that the object is referenced, it will be verified to be a valid object before it can be used. If you need to reference the object a lot, as with nested loops, or large recordsets, it is best to explicitly declare, instanciate, and release the objects.

    Code:
    Dim rst as ADODB.Recordset  ' Declare
    
    Set rst = New ADODB.Recordset  ' Instanciate
    
    ' Use the object
    
    rst.Close
    Set rst = Nothing  ' Release
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by loquin
    THey are both doing much the same thing. The first instance allows the connection.execute method to implicitly instanciate the object.

    I prefer the second approach.

    There is a third approach, where you implicitly instanciate the objects in the declaration.

    Code:
    dim rst as New ADODB.Recordset
    WHen an object is declared implicitly, its creation and release are handled automatically by VB at the same level of scope as where they are declared, so you should NOT release the objects yourself.

    While this has the effect of simplifying the source code, it has another side effect - the object code of an implicitly instanciated object runs a bit slower than does the object code of an explicitly instanciated object. In every location that the object is referenced, it will be verified to be a valid object before it can be used. If you need to reference the object a lot, as with nested loops, or large recordsets, it is best to explicitly declare, instanciate, and release the objects.

    Code:
    Dim rst as ADODB.Recordset  ' Declare
    
    Set rst = New ADODB.Recordset  ' Instanciate
    
    ' Use the object
    
    rst.Close
    Set rst = Nothing  ' Release

    Thanks for that loquin but I didn't understand a word of it.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    ref post #8
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •