VBA ADODB recoredset limits

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
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...

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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ScottR

Well-known Member
Joined
Apr 17, 2008
Messages
545
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

Active Member
Joined
Mar 23, 2002
Messages
423
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

Active Member
Joined
Mar 23, 2002
Messages
423

ADVERTISEMENT

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

Well-known Member
Joined
Apr 17, 2008
Messages
545
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

the error occurs at the transposedim line.
Kinda suggests to me your TransposeDim function is to blame.
 

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
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....

Code:
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,797
Office Version
  1. 2019
Platform
  1. Windows
Why bother? You have the data in Access. That's a better place for it than umpteen Excel worksheets. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,078
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top