CopyFromRecordset Question

[parityd]

HI,

I have a VB6 application that queries an Oracle Table and export the
data to an Excel 2003 workbook using the CopyFromRecordset method. I
have a procedure that will export the data into multiple worksheets if
there are more than 65536 rows. This works fine if the recordset has
not more than 65536 rows. When the recordset has more than 65536 rows,
the CopyFromRecordset method still only copies the first 65536 rows
onto the next worksheet.

I am using ADO 2.7 and Oracle version is 8i.

However when the above application queries an Access database that
returns more than 65536 rows (eg. 150,000 records), the
CopyFromRecordset method works fine copying are the 150,000 records
onto 3 worksheets.

Anyone who has run into this problem and may have a solution please
advise.

Thank you

parityd

 

[Guest]

Can you send us the applicable portion of your code: the part that copies to
multiple xl sheets ?

 

[parityd]

Below is the code segment that makes multiple xl sheets.

Do While Not padoRS.EOF = True
'Copy a maximum of 65530 records to the Excel worksheet starting on
row 2
If plngRecCount > 65530 Then
pobjWKBook.Sheets.Add after:=pobjWKBook.ActiveSheet
pobjWKBook.ActiveSheet.Name = "Page " & pintPageNum
pintPageNum = pintPageNum + 1
Set pobjWS = pobjWKBook.ActiveSheet
For pintColCount = 1 To pintFldCount
pobjWS.Cells(1, pintColCount).Value =
padoRS.Fields(pintColCount - 1).Name
Next
End If

pobjWS.Range(pobjWS.Cells(2, 1), _
pobjWS.Cells(65530,
pintFldCount)).CopyFromRecordset padoRS, 65530, pintFldCount
plngRecCount = plngRecCount + 65530
Loop

 

[Jamie Collins]

Below is the code segment that makes multiple xl sheets.

Using Excel's CopyFromRecordset on a recordset with more records than
the 65536 maximum worksheet rows does not cause the method to fail.
Rather, the cursor is merely moved e.g. to record 65537. Therefore, you
could do something like this:

Sub more_than_65536()
Dim rs As Object
Set rs = CreateObject("ADOR.Recordset")
rs.Open _
"SELECT * FROM 100K_row_table;", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"

Dim Counter As Long
With Workbooks("MyWorkbook.xls")
Do While Not rs.EOF
Counter = Counter + 1
.Worksheets(Counter).Range("A1") _
.CopyFromRecordset rs
Loop
End With
End Sub

Jamie.

--

 

[parityd]

Using CopyFromRecordset on a recordset retrieved from a Microsoft
Access table with more than 65536 records will work, it does moved the
cursor to the 65537th record and the subsequent worksheet will be
populated beginning with the 65537th record. However, when using
CopyFromRecordset on a recordset retrieved from an Oracle table with
more than 65536 records, the subsequent worksheet will be populated
beginning with the first record again. I was hoping to use
CopyFromRecordset (due to performance) instead of writing row by row.

Thanks
parityd