how to SPLIT an ADO recordset ?

Why not simply create two recordsets of 500 records each by using the appropriate SQL query to populate each recordset? Can you provide more information about what you're trying to accomplish?

thanks for ur reply...iam writing a procedure that exports records to excel...the problem is that excel only allows 65k records per sheet....i need to split the recordset so i can export each recordset to a new sheet

yes its a SQL Server database.....and yes there is a creditcard number field that could be used as a key field...thanks for ur reply

Can you perform the export with SQL directly?

Dim cnn As New ADODB.Connection
Dim strSQL As String
Dim rs As New ADODB.Recordset

cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Test Files\Book20.xls;" & _
"Extended Properties=""Excel 8.0;HDR=No"""

strSQL = "SELECT TOP 500 * INTO [Orders] FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes] ORDER BY OrderID;"

cnn.Execute strSQL

cnn.Close
Set cnn = Nothing

no iam trying to export the dats from the recordset into excel

Then use Phil's suggestion. You can also use the TOP statement (as in my example to limit the number of rows returned).

Is the original data source a SQL Server database? Does the table have a unique column on which you can sort the recordset? If the answer is yes to both questions, you can use SQL's SET ROWCOUNT statement to limit the number of rows returned by your query.
ok one last question...do i have to manually exceute some sort of command in SQL Server to sort the database of it is sorted by default ?