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.

Answer 1

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

Answer 2

The Access Database Engine doesn’t handle this very well: the proprietary TOP N syntax returns ties and the N cannot be parameterized; the optimizer doesn't handle the equivalent subquery construct very well at all :(

But, to be fair, this is something SQL in general doesn't handle very well. This is one of the few scenarios where I would consider dynamic SQL (shudder). But first I would consider using an ADO classic recordset, which has properties for AbsolutePage, PageCount, and PageSize (which, incidentally, the DAO libraries lack).

---

You could also consider using the Access Database Engine's little-known LIMIT TO nn ROWS syntax. From the Access 2003 help:

You may want to use ANSI-92 SQL for the following reasons... ...

• Using the LIMIT TO nn ROWS clause to limit the number of rows returned by a query

Could come in handy?

... my tongue is firmly embedded in my cheek :) This syntax doesn't exist in the Access Database Engine and never has. Instead, it's yet another example of the appalling state of the Access documentation on the engine side of the house.

Is the product fit for purpose if the documentation has massive holes and content cannot be trusted? is Caveat emptor.