RECORDSET to array so big

I use this code to store recorset into arrray but the getrow go in crash memory if the number of item from recordset is very big....
I have approx 600.000 records and 26 fields

I use DAO but naturally ADO code is welcome!

Is èpossible to store into array the complete recordset a "Piece to Piece" do until all records are stored into array?

note: for text i have used only 250.000
 
my code:

Option Explicit
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
    Dim WS As DAO.Workspace

    'Set dbs = CurrentDb
    Set WS = DBEngine.Workspaces(0)
    Set dbs = WS.OpenDatabase("C:\TEMP\mydb.mdb", True)
    Set rst = dbs.OpenRecordset("SELECT * FROM table", dbOpenSnapshot)

    With rst
        .MoveLast
        .MoveFirst
        avarOriginalArray = .GetRows(250000)
    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

Open in new window

LUCA1989Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Wayne Taylor (webtubbs)Connect With a Mentor AstronautCommented:
Just noticed another few Integer variables. You would do well to change all of them to Long.
0
 
Wayne Taylor (webtubbs)AstronautCommented:
The maximum number for an Integer type in VB6 is 32767. If you need more than that you should use Long, which can go as high as 2,147,483,647.

Specifically, you should change these two declarations....

    Dim intRecord As Integer
    Dim intRecordCount As Integer

Wayne
0
 
LUCA1989Author Commented:
ok changed the dim in long... but in other case have prob with memory in:

avarTransposedArray = TransposeArray(avarOriginalArray)

Have other suggestion or a different code?
Tks
0
All Courses

From novice to tech pro — start learning today.