VBA ADODB recoredset limits

[bolo]

Hi all, I have witten a small macro that will connect to an access database, use SQL to connect to a table and download the contents of said table into a spreadsheet. As the tables might be greater than 65536 rows (yes still using xl2003 here!!!!) i have used the getrows function to split the recordset and paste the data into separate sheets as necessary. The macro works fine, unless the table in question has more than 1,000,000 records. Then i get a runtime error 1004. I do not know the exact figure, but it seems to be 1 million. So is there a limit to using the adodb recordset and getrows function? Is it a memory issue?

Here is the code...

    Set tmpApp = New Excel.Application
    tmpApp.DisplayAlerts = False
    nSheets = WorksheetFunction.RoundUp(rs.RecordCount / nRec, 0)
    tmpApp.SheetsInNewWorkbook = nSheets
    Set tmpWb = tmpApp.Workbooks.Add
    ReDim tmpTit(rs.FIELDS.Count) As String
    For i = 1 To rs.FIELDS.Count
        tmpTit(i - 1) = rs.FIELDS(i - 1).Name
    Next i
    
    For i = 1 To nSheets
        Application.StatusBar = "Downloading page " & i & " of " & nSheets & "..."
        tmpWb.Sheets(i).[a1].Resize(1, rs.FIELDS.Count) = tmpTit
        tmpArray = rs.GetRows(nRec)
        tmpWb.Sheets(i).[a65536].End(xlUp).Offset(1, 0).Resize(UBound(tmpArray, 2) + 1, rs.FIELDS.Count) = TransposeDim(tmpArray)
    Next i

TransposeDim is just a function used to transpose the array, as application.transpose has limits to transposing lots of data...

Many Thanks

Bolo.

 

[bolo]

bump! anyone have any ideas?

 

[ScottR]

Haven't heard of one before on what line does the error occur?

The only remote possibility I can think of is the number of new sheets being incorrect which would be caused by rs.recordcount not returning the actual number of records.

Which in turn is caused by not doing an rs.movelast first.

Sorry, that really is reaching!

 

[bolo]

the error occurs at the transposedim line. I definitely have enough spreadsheets n the new workbook. I tried reducing nrec to 30000 which doubled the number of sheets created, so I don't. think this is. an issue

many thanks for your help and suggestions

 

[bolo]

Hi,

I have found that the magic number is 1,048,432.

If the table has more than this numbr of records the macro dies. It doesn't matter how mny sheets i create it just dies once it reaches this 1,048,433. Otherwise it works fine.

If someone has any other method of doing this i would love to learn it

Thanks

Bolo

 

[ScottR]

It's letting you create the recordset and array before terminating itself.

That suggests you can work around by finding an alternate method of returning the data to the Excel sheets.

If it is a memory issue be sure to disable screen updating and automatic calculations. That may help.

 

[Richard Schollar]

the error occurs at the transposedim line.

Kinda suggests to me your TransposeDim function is to blame.

 

[bolo]

Kinda suggests to me your TransposeDim function is to blame.

that is what i am thiking as wellbut i cann work u watis wrong with the function....

Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)
    
    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
    
    TransposeDim = tempArray
    Set tempArray = Nothing

End Function

 

[dk]

Hi

Maybe I've missed something but can you not just use the CopyFromRecordset method as opposed to going from a recordset to array and then to the range?

DK

 

[xenou]

Why bother? You have the data in Access. That's a better place for it than umpteen Excel worksheets.