Microsoft Access and paging large datasets

Question

Is there an easy way to page large datasets using the Access database via straight SQL? Let's say my query would normally return 100 rows, but I want the query to page through the results so that it only retrieves (let's say) the first 10 rows. Not until I request the next 10 rows would it query for rows 11-20.

Accepted Answer

If you run a ranking query, you will get a column containing ascending numbers in your output. You can then run a query against this column using a `BETWEEN...AND` clause to perform your paging.

So, for example, if your pages each contain 10 records and you want the third page, you would do:

```
SELECT * FROM MyRankingQuery WHERE MyAscendingField BETWEEN 30 and 39
```

How to Rank Records Within a Query Microsoft support KB208946

Microsoft Access and paging large datasets

Solution

Problem