Solved

RECORDSET to array so big

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB6 Compile Compatibility Issue 4 102
Best way to export and then import DHCP to a new server? 6 67
Pass through dll 2 81
Prevent user closing word document opened with VB6 6 74
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
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…

776 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