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.

 

[dk]

OK, have justed tested using Excel 2003 and got the same error message - what a pain!

Bolo - do you have any identifier that could be used to split your data into chunk of less than a million records such as a year? One slightly clunky workaround would be to loop through and select x number of records at a time but use the same technique.

DK

 

[bolo]

OK, have justed tested using Excel 2003 and got the same error message - what a pain!

Bolo - do you have any identifier that could be used to split your data into chunk of less than a million records such as a year? One slightly clunky workaround would be to loop through and select x number of records at a time but use the same technique.

DK

i DK,

Thanks for checking... I am not sure what i wll do about this i mght just leave it for the moment hoping no one ever downlods 1000000+ records. Also the company should be upgradng office next year so it might not be worth the hassle of solving this prolem.

Many thanks for your help DK