Solved

RECORDSET to array so big

Posted on 2010-11-21
3
461 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

920 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

12 Experts available now in Live!

Get 1:1 Help Now