ms-accesspagination

Microsoft Access and paging large datasets

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.

Solution

  • 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