converting recordset to array?

carnevil00 · February 28th, 2006, 04:56 PM

how do you convert a recordset to an array?
I have the beginning of the code and i also tried to use getrows and various other code i found on the forum but it is obviously very wrong the way i put it all together..

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String
Dim arrRows As Variant
Dim info(50) As String

Set db = CurrentDb

strSQL = "SELECT SerialCode.SerialCode FROM SerialCode WHERE ProjectionID = 4;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'intResult = rs("RecordCount")
'MsgBox intResult, vbOKOnly

' Load array
    arrRows = rs.GetRows()
    ' Print array elements
    For intRow = 0 To UBound(arrRows)
        info(intRow) = arrRows(intRow)
    Next intRow
rs.Close

End Sub

Bob Bedell · February 28th, 2006, 06:43 PM

Hello,

Using GetRows():

Code:

Sub LoadArray()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim avarRecords As Variant
    Dim intRecord As Integer

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tblRecords", dbOpenSnapshot)

    ' Must specify a number of rows to retrieve. GetRows() with no
    ' "numrows" parameter retrieves only 1 record. No problem if you
    ' specify more records than the record source contains.
    avarRecords = rst.GetRows(25)

    ' Print number of records retrieved.
    Debug.Print UBound(avarRecords, 2) + 1 & " records retrieved."

    ' Print out array data. My data source has 7 columns. Array
    ' returned by GetRows is 0-based.
    For intRecord = 0 To UBound(avarRecords, 2)
        Debug.Print "  " & _
            avarRecords(0, intRecord) & " " & _
            avarRecords(1, intRecord) & ", " & _
            avarRecords(2, intRecord) & ", " & _
            avarRecords(3, intRecord) & ", " & _
            avarRecords(4, intRecord) & ", " & _
            avarRecords(5, intRecord) & ", " & _
            avarRecords(6, intRecord)
    Next intRecord

    rst.Close
    dbs.Close

End Sub

Output looks like:

Code:

16 records retrieved.
  1 Text1, Memo1, 1, 1/1/2006, 1.5, True
  2 Text2, Memo2, 2, 1/2/2006, 2, False
  3 Text3, Memo3, 3, 1/3/2006, 3, True
  4 Text4, Memo4, 4, 1/4/2006, 4, False
  5 Text5, Memo5, 5, 1/5/2006, 5, True
  6 Text6, Memo6, 6, 1/6/2006, 6, False
  7 Text7, Memo7, 7, 1/7/2006, 7, True
  8 Text8, Memo8, 8, 1/8/2006, 8, False
  9 Text9, Memo9, 9, 1/9/2006, 9, True
  10 Text10, Memo10, 10, 1/10/2006, 10, False
  11 Text11, Memo11, 11, 1/11/2006, 11, True
  12 Text12, Memo12, 12, 1/12/2006, 12, False
  13 Text13, Memo13, 13, 1/13/2006, 13, True
  14 Text14, Memo14, 14, 1/14/2006, 14, False
  15 Text15, Memo15, 15, 1/15/2006, 15, True
  16 Text16, Memo16, 16, 1/16/2006, 16, False

HTH,

Bob

kindler · February 28th, 2006, 06:49 PM

You can also use the RecordCount property of the RecordSet to get all the records instead of using an arbitrary number like 25.

carnevil00 · February 28th, 2006, 07:58 PM

WORKS GREAT

UR GENIUSES

Bob Bedell · March 2nd, 2006, 03:46 AM

Actually, I wasn't real happy with this bit:

For intRecord = 0 To UBound(avarRecords, 2)
   Debug.Print " " & _
      avarRecords(0, intRecord) & " " & _
      avarRecords(1, intRecord) & ", " & _
      avarRecords(2, intRecord) & ", " & _
      avarRecords(3, intRecord) & ", " & _
      avarRecords(4, intRecord) & ", " & _
      avarRecords(5, intRecord) & ", " & _
      avarRecords(6, intRecord)
Next intRecord

Didn't like hard coding the arrays first dimension like that, but couldn't see how to do it dynamically.

Problem is GetRows() returns a bass ackwards array. The first dimension contains fields (columns), and the second dimension contains records (rows). So a standard multi-dimensional array output routine (using nested For statements) outputs your data backwards!! For example the following array iteration routine would give you the following output:

Code:

Sub PrintArray(a As Variant)
    Dim row As Integer, col As Integer
    Dim temp As String

    Debug.Print Space$(9) & temp
    For row = LBound(a) To UBound(a)
        For col = LBound(a, 2) To UBound(a, 2)
            temp = temp & Space$(4) & a(row, col) & " "
        Next col
        Debug.Print temp
        temp = vbNullString
    Next row

End Sub

Output

1 2 3 4
Text1 Text2 Text3 Text4
Memo1 Memo2 Memo3 Memo4
1 2 3 4
1/1/2006 1/2/2006 1/3/2006 1/4/2006
1.5 2 3 4
True False True False

We want:

1 Text1 Memo1 1 1/1/2006 1.5 True
2 Text2 Memo2 2 1/2/2006 2 False
3 Text3 Memo3 3 1/3/2006 3 True
4 Text4 Memo4 4 1/4/2006 4 False

So we gotta transpose the dimensions of the array GetRows() returns so that the first dimension contains records (rows), and the second dimension contains fields (columns). Then you can pass the array to a standard output routine, and avoid having to hard code your columns.

Here's the revised code with new TransposeArray and PrintArray subs (I also tossed in kindler's recordcount suggestion).

Code:

Sub RevisedLoadArray()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim avarOriginalArray As Variant
    Dim avarTransposedArray
    Dim intRecord As Integer
    Dim intRecordCount As Integer

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tblRecords", dbOpenSnapshot)

    With rst
        .MoveLast
        .MoveFirst
        avarOriginalArray = .GetRows(.RecordCount)
    End With

    ' Print number of records retrieved.
    Debug.Print UBound(avarOriginalArray, 2) + 1 & " records retrieved."

    ' Transpose array dimensions.
    avarTransposedArray = TransposeArray(avarOriginalArray)

    ' Output array data.
    Call PrintArray(avarTransposedArray)

    rst.Close: Set rst = Nothing
    dbs.Close: Set dbs = Nothing

End Sub

Function TransposeArray(v As Variant) As Variant
' Transpose dimensions of a 0-based
' multi-dimensional array

    Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant

    Xupper = UBound(v, 2)
    Yupper = UBound(v, 1)

    ReDim tempArray(Xupper, Yupper)
    For X = 0 To Xupper
        For Y = 0 To Yupper
            tempArray(X, Y) = v(Y, X)
        Next Y
    Next X

    TransposeArray = tempArray

End Function

Sub PrintArray(a As Variant)
    Dim row As Integer, col As Integer
    Dim temp As String

    Debug.Print Space$(9) & temp
    For row = LBound(a) To UBound(a)
        For col = LBound(a, 2) To UBound(a, 2)
            temp = temp & Space$(4) & a(row, col) & " "
        Next col
        Debug.Print temp
        temp = vbNullString
    Next row

End Sub

Here's the output:

Code:

16 records retrieved.

  1     Text1     Memo1     1     1/1/2006     1.5   True           
  2     Text2     Memo2     2     1/2/2006     2.5   False           
  3     Text3     Memo3     3     1/3/2006     3.5   True           
  4     Text4     Memo4     4     1/4/2006     4.5   False           
  5     Text5     Memo5     5     1/5/2006     5.5   True           
  6     Text6     Memo6     6     1/6/2006     6.5   False           
  7     Text7     Memo7     7     1/7/2006     7.5   True           
  8     Text8     Memo8     8     1/8/2006     8.5   False           
  9     Text9     Memo9     9     1/9/2006     9.5   True           
 10     Text10    Memo10   10     1/10/2006   10.5   False           
 11     Text11    Memo11   11     1/11/2006   11.5   True           
 12     Text12    Memo12   12     1/12/2006   12.5   False           
 13     Text13    Memo13   13     1/13/2006   13.5   True           
 14     Text14    Memo14   14     1/14/2006   14.5   False           
 15     Text15    Memo15   15     1/15/2006   15.5   True           
 16     Text16    Memo16   16     1/16/2006   16.5   False

HTH,

Bob

Bob Bedell · March 2nd, 2006, 03:50 AM

You can loose the following variable declaration:

Dim intRecord As Integer

Was needed in the old, hard-coded sub, but not in the revised version.

Bob

bhaks · September 28th, 2006, 03:52 PM

in the TransposeArray function, i am getting an
"Expected end of statement" error on the line - "Next Y"

i tried the code in asp

pls, help.

Bob Bedell · September 28th, 2006, 05:09 PM

Hi bhaks,

I'd suggest either cut and paste the code into your app. Its debugged. Or post the code thats giving you trouble and we'll debug it. You're getting a compile-time error so something is wrong with the syntax of you For Next loop.

Best,

Bob

bhaks · October 2nd, 2006, 02:43 PM

ya that's true.
i removed the X and Y following the "Next" syntax of the for loop.it worked.
thanx.