Excel's 65,536 rows limitation

[SkyHigh]

Hi Folks
Is there a workaround on the subject limit, or how can I direct the output to a new worksheet once it reaches the max rows limit

Here's the snippet of the code I am using :

***

Set RsrDetail = db.OpenRecordset("SELECT * FROM tblOrderDetails")

nbRows = WS.Range("A7").CopyFromRecordset(RsrDetail)

***

Thanks a lot

 

[Craig0201]

Use SELECT TOP 65536.

Something like.....

'Indicator of last record retrieved
LastRecordRetrieved = 0
'Counter to state what worksheet to output to
i = 1
'Count of records retrieved so knows that reached last worksheet
RecordsRetrieved = 65536

'Check has filled a whole worksheet or is first sheet
Do While RecordsRetrieved = 65536
'Open recordset to next 65536 records
Set RsrDetail = db.OpenRecordset("SELECT TOP 65536 * FROM tblOrderDetails WHERE YourPK > " & LastRecordRetrieved & " ORDER BY YourPK")
'Move last to retrieve PK so can retrieve next recordset
RsrDetail.MoveLast
'Retrieve PK
LastRecordRetrieved = YourPK
'Count records retrieved so know that end reached
RecordsRetrieved = RsrDetail.RecordCount
'Output rows
nbRows = WB.Worksheets(i).Range("A7").CopyFromRecordset(RsrDetail)
'Increment sheet counter
i = i + 1
Loop

Craig