Select 10 rows at a time

[jenlid]

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.

[kovan]

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

[Lafor]

SET ROWCOUNT 10

prior to running your query

[monte96]

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..

[parksie]

Try the LIMIT statement.

[Gaffer]

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

[Lafor]

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

[parksie]

Use this:

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

...and so on.

[Lafor]

does not on SQL server though

[kovan]

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