Excel CopyFromRecordset limit 1

[Andrzejek]

I have a small app where I use Excel to dump the data from data base.
I create an ADODB recordset and use:

Range("A2").CopyFromRecordset MyRecordSet

All works OK, unless I have a recordset with a large amount of data (rows).
This code takes OK recordsets up to 65536 rows of data, but no more.

I have some recordsets that have around 223 915 records, but only 65536 of them end up in my file. The rest are lost (?)

I did try to specify the Max number of rows, but that does not help:

Range("A2").CopyFromRecordset MyRecordSet, [red]5000000[/red]

Is there a limit of rows allowed in CopyFromRecordset?
What do I need to do to 'grab' all rows from my rst and dump it into Excel?

Could that be the limit of my computer's memory?

PS. Just tried the same code on other computer with newer version of Excel (1908 with the limit vs 2108 and all is well), so that must be it... (?)


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

There is no CopyFromRecordset limit, at least in excel 2016 (2208), however I found claims referring to excel 365.
I have no problems to get +400k records, 11 columns, with (reference to ADO, both 2.8 and 6.1 version works):

Sub CopyFromAccess()
Dim DBFullName As String
Dim strConn As String, strSQL As String
Dim conn As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim i As Integer

DBFullName = "D:\xxxx.accdb"
strSQL = "SELECT ... FROM ... WHERE ..."

Set conn = New ADODB.Connection
strConn = "Provider = Microsoft.ACE.OLEDB.12.0;"
strConn = strConn & "Data Source=" & DBFullName
conn.Open ConnectionString:=strConn

Set rsData = New ADODB.Recordset

With rsData
    .Open Source:=strSQL, ActiveConnection:=conn
    
    ' headers
    For i = 0 To .Fields.Count - 1
        Range("A1").Offset(0, i).Value = .Fields(i).Name
    Next
    
    ' data
    ThisWorkbook.Worksheets(1).Range("A2").CopyFromRecordset rsData
End With

Set rsDdata = Nothing
conn.Close
Set conn = Nothing
End Sub

combo

 

[Andrzejek]

Thank you Combo

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

Thanks, only records starting from cursor are transferred in my case. This feature may be used to cut recordset to fit worksheet size.

combo