Issue with copyfromrecordset and cursorlocation

[MaureenL]

I am trying to copy a large ADO recordset to Excel.

If I define the cursorlocation as adUseServer , the rst copies the first 64999 records over, loops and then copies the remaining 9000 records. I can't define it this way or I get other issues on some people's pcs " Error Number: 430 Error Description: Class does not support Automation or does not support expected interface".

If I define the cursorlocation as adUseClient , the statement copies the first 64999 over and over and over again.

I think I have to adjust my sql to only pull back 64999 at a time. This isn't simple because this is called from multiple different areas. Are there any other ideas?

Here is the code:
Dim rst As Object

Dim fldCount As Integer
Dim recCount As Long
Dim i As Integer
Dim sheetName As String
Dim xlWs As Object

On Error GoTo ErrorHandler

Set rst = CreateObject("ADODB.Recordset")

conSybase.CommandTimeout = 0
rst.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rst.Open sql, conSybase, adOpenStatic, adLockReadOnly

record_cnt = rst.RecordCount
sheetName = workSheetName

While Not rst.EOF
If (i > 1) Then
sheetName = sheetName & "_" & i
End If

Set xlWs = xlWb.sheets.Add
xlWs.name = sheetName

' Copy field names to the first row of the worksheet
fldCount = rst.Fields.count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).name
Next
xlWs.range("A2").CopyFromRecordset rst, 64999
Wend

' Close ADO objects
rst.Close
Set rst = Nothing
If (record_cnt <> 0) Then
xlWs.Rows(1).Font.Bold = True
Set xlWs = Nothing
End If

 

[SkipVought]

Hi

the rst copies the first 64999 records over, loops and then copies the remaining 9000 records.

emphasis mine

Excel ONLY has 65,536 rows available on each worksheet. I guess that would error out!

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[MaureenL]

That is why on each loop I am adding another worksheet with an additional number. The problem is with adUseClient it doesn't remember when it is. adUseServer will end up with one worksheet with 64999, and another worksheet with 9000,