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.

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

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.

I'm not certain how ranking answers your question. Also, I'm having trouble imagining why you would need this -- this is usually something you do on a website in order to break down the data retrieved into small chunks. But a Jet/ACE database is not a very good candidate for a website back end, unless it's strictly read-only.

One other SQL solution would use nested TOP N, but usually requires on-the-fly procedural code to write the SQL.

It also has the problem with ties, in that unless you include a unique field in your ORDER BY, you can get 11 records with a TOP 10 should two records have a tie on the values in the ORDER BY clause.

I'm not suggesting this is a better solution, just a different one.