CopyFromRecordset method isn't moving the cursor to last row output

Question

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 here?

i dont believe this is a property of copyfromrecordset. since it is a range method the cursor shouldn't move from the range you apply it to. It is also not mentioned in the [documentation](http://msdn.microsoft.com/fr-fr/library/microsoft.office.interop.excel.range.copyfromrecordset(v=office.11).aspx) — scott, May 28 '13 at 20:27
`rs.GetRows` will move the cursor, but you also must specify in advance the max. number of rows to retrieve, and you will also need to write code to transpose the array it returns, since it will give you rows-as-columns. In the end you'd do just as well to loop through the recordset one row at a time to fill an array, and then paste the array to the worksheet. — Chel, May 28 '13 at 22:32

score 1 · Answer 1 · answered Oct 28 '13 at 14:43

this is what I used:

Dim iRow, iColumn, iSheet As Integer

iSheet = 1
iRow= 1

With oLibrExce
        .Visible = True
        .Workbooks.Add

    While Not record.EOF
        .Worksheets.Add
        Set oHojaExce = .ActiveWorkbook
        With oHojaExce.Worksheets(1)
            .Activate
            .Name = tableName & iSheet 
            For iColumn = 0 To cantidadColumnas - 1
                .cells(1, iColumn + 1) = Columnas(iColumn)
                .cells(1, iColumn + 1).Font.Bold = True
                If TipoColumnas(iColumn) = "DATE" Then
                    .Columns(iColumn + 1).Select
                    .Columns(iColumn + 1).NumberFormat = "m/d/yyyy"
                End If
                If TipoColumnas(iColumn) = "NUMBER" Then
                    .Columns(iColumn + 1).Select
                    .Columns(iColumn + 1).NumberFormat = "0.00"
                End If
            Next
            .range("A2").copyfromrecordset record, 1048576
            iSheet = iSheet + 1
        End With
        .cells.EntireColumn.AutoFit
        .cells(1, 1).Select
    Wend
End With

score 1 · Answer 2 · answered Apr 07 '22 at 00:29

I know this is way too late to help OP, but this was one of the top results when I searched for another issue, and it was never solved, so I'm providing this answer in case future searchers find this page

By specifying the MaxRows argument to CopyFromRecordset you will get the desired result

CopyFromRecordset(Data As Unknown, [MaxRows], [MaxColumns])

score 0 · Accepted Answer · 2013-05-28T14:21:48.897

0

This line

.Range("B2").CopyFromRecordset adoRS

pastes the entire recordset placing it at B2 as the left-top coordinates.

So, remove your loop and the above line.

You can loop through the recordset like this(pseudocode):

Function pasteRecordSet(ByRef adoRS)
    For i = 1 To adoRS.RecordCount
        If i > 65536 Then
            new sheet
            + you can call here recursively
        Else ' on the current sheet
            adoRS.MoveNext
            If (adoRS.EOF) Then
                adoRS.MoveFirst
            End If
    Next i
End Function

edited May 28 '13 at 14:21

answered May 28 '13 at 14:14

well, the idea is to use **CopyFromRecordset** multiple times, but it isn't behaving as I expected - looping through the records one by one isn't what I want. Thanks anyway. – Our Man in Bananas May 28 '13 at 14:34

CopyFromRecordset method isn't moving the cursor to last row output

3 Answers3