Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open a ADOB recorset with a SQL string (2003)

    I have a query that give the following SQL string:

    SELECT tblItemSAV.Itemn, tblItemSAV.ItemLike, tblItemSAV_1.Itemn
    FROM tblItemSAV, tblItemSAV AS tblItemSAV_1
    WHERE (((tblItemSAV.ItemLike) Is Not Null And Len([tblItemSAV.Itemlike])>"6") AND ((tblItemSAV_1.Itemn) Like [tblItemSAV].[ItemLike] & "*"))
    ORDER BY tblItemSAV.Itemn, tblItemSAV_1.Itemn;

    I try to open a ADOB recordset to reproduce the results of this query, the code is:

    'open a recordset from tblItemSAV
    strSQL1 = "SELECT tblItemSAV.Itemn, tblItemSAV.ItemLike, tblItemSAV_1.Itemn "
    strSQL1 = strSQL1 & "FROM tblItemSAV, tblItemSAV AS tblItemSAV_1 "
    strSQL1 = strSQL1 & "WHERE (((tblItemSAV.ItemLike) Is Not Null) And "
    strSQL1 = strSQL1 & "((tblItemSAV_1.Itemn) Like [tblItemSAV].[ItemLike] & '*') And "
    strSQL1 = strSQL1 & "((Len([tblItemSAV.Itemlike])) > '6')) "
    strSQL1 = strSQL1 & "ORDER BY tblItemSAV.Itemn, tblItemSAV_1.Itemn;"

    Set rst2 = New ADODB.Recordset
    rst2.Open strSQL1, cnn, adOpenDynamic, adLockOptimistic, adCmdText

    MsgBox rst2.RecordCount

    The query returns 517 records but the rst2 recordset is empty why?

    Marcel

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Open a ADOB recorset with a SQL string (2003)

    Did you test results of recordset? If using adOpenDynamic cursor, RecordCount will return either -1 (there are records) or 0 (no records) - because the number of records in a dynamic recordset can change, as name implies. See MSKB 194973:

    ADO: Recordcount May Return -1

    The syntax used to open recordset looks OK. Sample sub using Northwind Orders table:

    <code>Sub TestADORecordset()</code>

    <code> Dim cnn As ADODB.Connection</code>
    <code> Dim rst As New ADODB.Recordset</code>
    <code> Dim vArray As Variant</code>
    <code> Dim strSQL As String</code>

    <code> Set cnn = CurrentProject.Connection</code>
    <code> strSQL = "SELECT ORDERID FROM ORDERS;"</code>
    <code> rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic, adCmdText</code>

    <code> ' if adOpenDynamic cursor, RecordCount will return -1 or 0:</code>
    <code> MsgBox rst.RecordCount, vbInformation, "RECORD COUNT"</code>

    <code> vArray = rst.GetRows</code>
    <code> ' GetRows returns 2-D, zero-based array in Col/Row order:</code>
    <code> MsgBox UBound(vArray, 2) + 1, vbInformation, "RECORD COUNT"</code>

    <code> Erase vArray</code>
    <code> rst.Close</code>

    <code> ' Use adOpenKeyset cursor:</code>
    <code> rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText</code>
    <code> MsgBox rst.RecordCount, vbInformation, "RECORD COUNT"</code>

    <code> rst.Close</code>
    <code> Set rst = Nothing</code>
    <code> Set cnn = Nothing</code>

    <code>End Sub</code>

    Note the first recordset RecordCount was -1. You could copy recordset into an array using GetRows, then check UBound of 2nd dimension (GetRows returns a 2-dimensional, zero-based array in Column/Row order), but it would not be very efficient to copy large recordset into an array just to get a recordcount. (The GetRows & 2nd recordset both correctly indicated 830 records in Orders table.) Recommend use adOpenKeyset cursor if need recordcount (recordset will be updatable). If your recordset is actually empty, would not know cause.

    HTH

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a ADOB recorset with a SQL string (2003)

    Hello,
    I apologize to not getting back to you sooner but I was on vacation for some weeks. Anyway thank you for your help, it was working after I change the wildcard * to %, the _ by [_] in the sql statement and of course the adOpenDynamic cursor by a static one.

    Marcel

Posting Permissions

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