CopyFromRecordset method stops at 65536 records

[pwl2706]

I am using ADO to query a table in MS Access 2003, and outputting the data to Excel 2003 worksheets using the CopyFromRecordset method.

The table has more than 65536 records, so I cannot use DoCmd.TransferSpreadsheet and need to use VBA with ADO.

My problem is that after making a call to CopyFromRecordset even though only 65536 records are output, the cursor stays at 1 (AbsolutePosition=1) when according to my understanding, the cursor should be at 65537, ready for the next call to CopyFromRecordset

Here below is the code I am using:

Dim oXL As Excel.Application
Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim iIndx As Integer

Dim blnMultipleSheets As Boolean

Set adoConn = New ADODB.Connection
Set adoRS = New ADODB.Recordset

With adoConn
    .CursorLocation = adUseClient
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=O:\Dev\Support\Recurring_Requests\Future_Deals_Notice_InterestValues_Rates_Data\Future Deals.mdb;Persist Security Info=False"
    .Open
End With

With adoRS
    .CursorType = adOpenForwardOnly
    .ActiveConnection = adoConn
    .CursorLocation = adUseClient
    .Source = "SELECT * FROM Future_Deals_InterestValues_Rates_Data"
    .Open
End With

Set oXL = New Excel.Application

With oXL
    If .Version < 12 Then
        blnMultipleSheets = True
    Else
        blnMultipleSheets = False
    End If

    .Visible = True
    .Workbooks.Add

    .Range("B2").CopyFromRecordset adoRS
    If adoRS.RecordCount > .ActiveSheet.Rows.Count Then
        Do While Not adoRS.EOF
            .Sheets.Add
            Range("B2").CopyFromRecordset adoRS
        Loop
    End If
End With

so what am I missing please?

 

[anvg]

Try this change

    If adoRS.RecordCount > .ActiveSheet.Rows.Count Then
        Do
            adoRS.Move ActiveSheet.Rows.Count - 1, adBookmarkCurrent
            If adoRS.EOF Then Exit Do
            .Sheets.Add
            Range("B2").CopyFromRecordset adoRS
        Loop
    End If

Regards,