How do I implement pagination in SQL for MS Access?

Question

I'm accessing a Microsoft Access 2002 database (MDB) using ASP.NET through the OdbcConnection class, which works quite well albeit very slowly.

My question is about how to implement pagination in SQL for queries to this database, as I know I can implement the TOP clause as:

SELECT TOP 15 *
FROM table

but I am unable to find a way to limit this to an offset as can be done with SQL Server using ROWNUMBER. My best attempt was:

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

which fails with:

Error Source: Microsoft JET Database Engine

Error Message: No value given for one or more required parameters.

I can't work out this error, but I'm assuming it has something to do with the sub-query that determines a rownumber?

Any help would be appreciated with this; my searches on google have yielded unhelpful results :(

Answer 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 becuase 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 resulkt you want.

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