• Fill array from recordset

    • This topic has 4 replies, 3 voices, and was last updated 16 years ago.
    Author
    Topic
    #458528

    Hi,

    (Env: Excel O’2003, DAO 3.6)

    I’m running a query to a DB and got a functioning SQL string that returns a recordset which is a two dimensional array by say 10 cols by X rows

    What is the best way to put this into an array?

    Public Function SQL_QueryForArray(ByRef SQL_QueryForArray As String) as Variant

    sSQL = SQL_QueryForArray

    Set qy = con.CreateQueryDef(“”, sSQL)
    Set rs = qy.OpenRecordset(dbOpenForwardOnly, 0, dbReadOnly)

    If rs.EOF Then
    MsgBox = “Not available in the database”
    Else
    ‘Problem is here – how do I load the recordset into an array??
    SQL_QueryForArray = rs.Fields().Value
    End If

    Set rs = Nothing
    Set qy = Nothing

    End Function

    Will I have to loop the recordset and fill it into the array, one row at a time – or can it be done in a smarter way??

    Viewing 0 reply threads
    Author
    Replies
    • #1153287

      You can use rs.GetRows but you will then need to transpose the array to get it rows*columns rather than the other way around.

      • #1159550

        Hi Rory,

        Appreciate the answer, seems to work fine. However I’ve stombled on a new problem…
        I’m using DAO and a ‘forward-only’ recordset, so neither .MoveLast nor .RecordCount makes much sense here.

        Currently I’m doing:
        arrTemp = rs.GetRows()
        ‘arrTemp = rs.GetRows(8)

        debug.print UBound(arrTemp)

        When I leave the number of rows to extract ’empty’, .GetRows return a single row only. But when I immediately after do a UBound on the array returned (the debug.print part), then I get the correct actual number (8 in this case).

        I’ve read articles that suggests to do a SELECT count(*) prior to using .getrows. But having to do an extra query just to get a count, doesn’t really sound optimal, does it?

        Thus, by your experience what is the easiest way to know the number of rows returned in the recordset?

        • #1159553

          Hi Rory,

          Appreciate the answer, seems to work fine. However I’ve stombled on a new problem…
          I’m using DAO and a ‘forward-only’ recordset, so neither .MoveLast nor .RecordCount makes much sense here.

          Currently I’m doing:
          arrTemp = rs.GetRows()
          ‘arrTemp = rs.GetRows(8)

          debug.print UBound(arrTemp)

          When I leave the number of rows to extract ’empty’, .GetRows return a single row only. But when I immediately after do a UBound on the array returned (the debug.print part), then I get the correct actual number (8 in this case).

          I’ve read articles that suggests to do a SELECT count(*) prior to using .getrows. But having to do an extra query just to get a count, doesn’t really sound optimal, does it?

          Thus, by your experience what is the easiest way to know the number of rows returned in the recordset?

          Try a dcount inxtruction, look in the help for its use.

          • #1159575

            Thanks patt. Afraid I can’t find the dcount instruction in the help for DAO recordsets…

            Thus, I tried a different approach. I changed the recordset type to ‘dbOpenDynamic’ – keeping it read-only.
            – then I can use .MoveLast, .MoveFirst.

            Code is now:

            Set rs = qy.OpenRecordset(dbOpenDynamic, 0, dbReadOnly)

            If rs.EOF Then
            MsgBox “Not available in the database”, vbOKOnly
            Else
            rs.MoveLast
            i = rs.AbsolutePosition + 1
            rs.MoveFirst
            arrTemp = rs.GetRows(i)
            End If

            This fixed it. Appreciate your assistance.

    Viewing 0 reply threads
    Reply To: Fill array from recordset

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: