Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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??
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='Henrik Ryberg' post='774065' date='07-May-2009 21:04']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?[/quote]
    Try a dcount inxtruction, look in the help for its use.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

Posting Permissions

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