Solved

RECORDSET to array so big

Posted on 2010-11-21
3
453 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)
Comment Utility
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
Comment Utility
Just noticed another few Integer variables. You would do well to change all of them to Long.
0
 

Author Comment

by:LUCA1989
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now