How do I implement pagination in SQL for MS Access?

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) subOrderedORDER 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.

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 clientsORDER 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).

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