Solved

RECORDSET to array so big

Posted on 2010-11-21
3
469 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:LUCA1989
  • 2
3 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 34184234
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
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 34184240
Just noticed another few Integer variables. You would do well to change all of them to Long.
0
 

Author Comment

by:LUCA1989
ID: 34190292
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question