In this article we are going to see a working example of SQL pagination with Microsoft Access.
SQL Pagination
Microsoft Access doesn't have, by default, a SQL keyword that enable us to implement the pagination. We can get around this problem by using two nested queries.
Let's suppose we have a table with 17 rows, and we want to display 4 rows per page. The only value that we really need before creating the query is the total number of rows, as shown in the following code:
Select Count(*) As TotRows From SampleTable
the query we are going to use to display each page is:
Select TOP Row_Per_Page * From [ Select TOP (TotRows - ((Page_Number - 1) * Row_Per_Page) From SampleTable Order By ColumnName DESC ] Order By ColumnName ASC
in the illustration below we have these initial settings:
- Row_Per_Page = 4
- TotRows = 17
- Page_Number = 2
Page 1 (Page Size = 4)
Page 2 (Page Size = 4)
Now we can display each page with these queries:
--Page 1 SELECT TOP 4 * FROM [SELECT TOP 17 * FROM SampleTable ORDER BY ID DESC; ]. AS t1 ORDER BY ID; --Page 2 SELECT TOP 4 * FROM [SELECT TOP 13 * FROM SampleTable ORDER BY ID DESC; ]. AS t1 ORDER BY ID; --Page 3 SELECT TOP 4 * FROM [SELECT TOP 9 * FROM SampleTable ORDER BY ID DESC; ]. AS t1 ORDER BY ID; --Page 4 SELECT TOP 4 * FROM [SELECT TOP 5 * FROM SampleTable ORDER BY ID DESC; ]. AS t1 ORDER BY ID; --Page 5 SELECT TOP 4 * FROM [SELECT TOP 1 * FROM SampleTable ORDER BY ID DESC; ]. AS t1 ORDER BY ID;
borj