retrieving a number of records from a sql query

[fenris]

What I want to do is get the first 100 records from the database, in a sorted order, and display them to the user. Then when the user wants the next chunk of 100 records, when they click the button. I want the user to be able to cycle through to the end in chunks of 100 at a time. I am using ado and I would like to use sql as well. I can grab all the records with no problems, and I can also filter them based on critera. But I am at a loss at how to do this.

Any help is greatly appreciated....

Troy Williams B.Eng.
fenris@hotmail.com

 

[MatsHulten]

Try this:

recordset.CachSize = 100
recordset.Open Source, ActiveConnection, CursorType, LockType, adAsyncFetch '<= adAsyncFetch, see below!

CachSize limits the number of records returned from the DB, when you reach the end another 100 (CacheSize) is retrieved.

adAsyncFetch returns the CacheSize number of records from DB, then turns over control to the calling procedure (as above). However it also continues to fetch the remainder of the records in the background. This eliminates the wait when you want to retrive the CacheSize + 1 record (in this case record 101).

Test against EOF as usual to see if there are more records to fetch, and of course you need to sort the records in your sql-query, but as I understood you had already solved that part.

Good Luck
-Mats

 

[fenris]

Thanks for the reply, I'll have to try it as soon as I get home !


Thanks....

Troy Williams B.Eng.
fenris@hotmail.com