Clever Geek Handbook

Microsoft Access and paging large datasets

Is there an easy way to host large datasets with an Access database via straight SQL? Let's say my query usually returns 100 rows, but I want the query to go through the results so that it only fetches (let's say) the first 10 rows. Until I ask for the next 10 lines, it will ask for lines 11-20.

+2


source to share


3 answers


If you run a ranking query, you will get a column containing ascending numbers in your issue. You can then query that column with a suggestion BETWEEN...AND

to complete your lookup.

So, for example, if your pages contain 10 posts and you want a third page, you should:



SELECT * FROM MyRankingQuery WHERE MyAscendingField BETWEEN 30 and 39

How to rank records in a query
Microsoft Support KB208946

+2


source


The access database engine does not deal with this: the proprietary syntax TOP N

returns links, and the parameter N

cannot be parameterized; the optimizer does not handle the equivalent subquery construct at all :(

But, to be honest, this is something that SQL doesn't do very well at all. This is one of the few scenarios where I would consider dynamic SQL (shudder). But first I would consider using the classic ADO Recordset, which has properties for AbsolutePage

, PageCount

and PageSize

(which by the way is missing from the DAO libraries).

You can also use the obscure syntax LIMIT TO nn ROWS

for the Access Database Engine. From Access 2003 Help :



You can use ANSI-92 SQL for the following reasons ......

  • Using a clause LIMIT TO nn ROWS

    to limit the number of rows returned by a query

It can be useful?

... my language is firmly embedded in my cheek :) This syntax does not exist in the Access Database Engine and never has. Instead, this is yet another example of the horrific state of Access documentation on the home engine side.

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

+2


source


I'm not sure how the rating answers your question. Also, it's hard for me to understand why you need it - usually what you do on a website to break down the data received into small chunks. But the Jet / ACE database is not a very good candidate for a website end, unless it is strictly read-only.

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

This also has a problem with relationships, in the event that you did not include a unique field in your ORDER BY, you can get 11 records from the TOP-10 if two records are bound to the values ​​in the ORDER BY clause.

I am not suggesting that this is a better solution, just another one.

+1


source






More articles:


All Articles