Select 10 rows at a time

Is it possible to run a select statement, e.g "SELECT * FROM <TABLE>" where the table contains 100 rows, and only get for example 10 rows at a time?

I don´t want to fill my recordset with all 100 rows at once, I just want to fill it with 10 rows at a time and then empty the rs and fill it up with another 10 rows.

I´m going to create a searchfunction which returns 10 rows per page, like Altavista and so on. I have found out that filling the recordset with all rows at once could make it all rather slow.

use ado's .pagesize, .pagecount. .absoluteposition

SET ROWCOUNT 10

prior to running your query

Set rowcount will not do it. Because it will return the same 10 rows each time.
Set rowcount is usefull if you want to get a sample of the data.
Kovan's correct, the pagesize (which is 10 records by default by the way), pagecount, and absoluteposition properties are what your looking for..

Try the LIMIT statement.

Fine if you aren’t using DAO…

Funnily enough, I have to do a similar process, but using Access97 only. Although I haven’t started, I expect my approach will be using bookmarking in recordsets

Gaffer

Sorry... I misread the question

Hi, how about this for an idea...

"Select * from <table name> where id >= 1 and id < 10"

After this i would store my last id into a temporary variable and then use this value in my subsequent query

"Select * from <table name> where id >= " & myvariable & " and id < " & myvariablevalue + 10 & "

Should be something similar to this...

Sorry if this doesnt help you...all the best...vijay

Use this:

Code:

---

SELECT * FROM table LIMIT 0, 10
SELECT * FROM table LIMIT 10, 20

---

...and so on.

does not on SQL server though

like i said in my original post
use the ADO .pagesize, .absoluteposition and .pagecount
very simple..