How do I implement pagination in SQL for MS Access?

Solution 1:

If you wish to apply paging in MS Acces use this

SELECT *
FROM (
    SELECT Top 5 sub.ClientCode
    FROM (
        SELECT TOP 15 tblClient.ClientCode
        FROM tblClient
        ORDER BY tblClient.ClientCode
    ) sub
   ORDER BY sub.ClientCode DESC
) subOrdered
ORDER BY subOrdered.ClientCode

Where 15 is the StartPos + PageSize, and 5 is the PageSize.

EDIT to comment:

The error you are receiving, is because you are trying to reference a column name assign in the same level of the query, namely rownumber. If you were to change your query to:

SELECT *
FROM (
    SELECT ClientCode,
           (SELECT COUNT(c2.ClientCode)
            FROM tblClient AS c2
            WHERE c2.ClientCode <= c1.ClientCode) AS rownumber                
    FROM tblClient AS c1
)
WHERE rownumber BETWEEN 0 AND 15

It should not give you an error, but i dont think that this is the paging result you want.

Solution 2:

See astander's answer for the original answer, but here's my final implementation that takes into account some ODBC parser rules (for the first 15 records after skipping 30):

SELECT *
FROM (
  SELECT Top 15 -- = PageSize
  *
  FROM
  (
   SELECT TOP 45 -- = StartPos + PageSize
   *
   FROM tblClient
   ORDER BY Client
  ) AS sub1
  ORDER BY sub1.Client DESC
 ) AS clients
ORDER BY Client

The difference here is that I need the pagination to work when sorted by client name, and I need all columns (well, actually just a subset, but I sort that out in the outer-most query).

Solution 3:

I use this SQL code to implement the pagination with Access

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

I've published an article with some screenshots on my blog