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 :(

Mcdevitt

This Q has 59 views (at the time) and I do not see a single upvote. Is this just a DBA thing? – Pauwles

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

Pauwles

Thanks! Although I was getting some problems still due to the ODBC SQL parser (see my answer). – Mcdevitt

Of course, yes slaps head Could have also used a HAVING clause I think, but I'm not sure with JET - it's like trying to work with SQL coated in broken glass. – Mcdevitt

@Codesleuth: Every db engine has its own dialect of SQL. If you want to use Jet/ACE as a back end, you need to learn its SQL dialect, rather than unreasonably expecting it to be exactly like whatever SQL dialect(s) you already happen to know. I question the wisdom of the basic setup, i.e., using a Jet/ACE datastore behind a web application. It can work well for small user populations for mostly read-only operations, but won't scale. – Coronary

Quite obviously the reason I am using an Access Database is because the choice of database engine is out of my control. I'm an SQL Server programmer on a daily basis, which is why the differences are driving me mad. For example, chaining INNER JOIN statements appear to require parenthesis around each set of joins; I can't see why this is necessary, but I've learned this now and I can use it from now on. If I had my way, this would be entirely in SQL Server. The database is already struggling to cope with our 15 users, so perhaps I will get to change it soon. – Mcdevitt

@David W. Fenton: You are describing a problem that was solved years ago by ISO/ANSI Standard SQL. If the Access Database Engine doesn't support Standards then skills aren't transferrable so the code gets ported at the earliest opportunity. I imagine those application that remain unportable represent good business for Access specialist such as yourself which is presumably why your don't write portable code ;) – Biogeochemistry

The (barely) implicit assertion that I purposely write code to restrict my clients to Access is offensive to me, but not unexpected from someone with your history. – Coronary

@Codesleuth: if you're using a particular db engine and you are frustrated by differences in the SQL dialect, it's your unfamiliarity that is the problem, not the dialect. – Coronary

@David W. Fenton: "Look for Smileys, grins, and other 'Emoticons'. When you see one, the preceding statement was not meant to be really serious, don't take it as such." – Biogeochemistry

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

Mcdevitt

Answer 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

Keos

What's the point of "your" "blog"? – Amytal

Answer 4

This is the simple method of pagination using OleDbDataAdapter and Datatable classes. I am using a different SQL command for simplicity.

        Dim sSQL As String = "select Name, Id from Customer order by Id"
        Dim pageNumber As Integer = 1
        Dim nTop As Integer = 20
        Dim nSkip As Integer = 0
        Dim bContinue As Boolean = True
        Dim dtData as new Datatable
        Do While bContinue

            dtData = GetData(sSQL, nTop, nSkip, ConnectionString)

            nSkip = pageNumber * nTop
            pageNumber = pageNumber + 1

            bContinue = dtData.Rows.Count > 0
            If bContinue Then
                For Each dr As DataRow In dtData.Rows
                    'do your work here
                Next
            End If
        Loop

Here is the GetData Function.

    Private Function GetData(ByVal sql As String, ByVal RecordsToFetch As Integer, ByVal StartFrom As Integer, ByVal BackEndTableConnection As String) As DataTable
    Dim dtResult As New DataTable
    Try
        Using conn As New OleDb.OleDbConnection(BackEndTableConnection)
            conn.Open()
            Using cmd As New OleDb.OleDbCommand
                cmd.Connection = conn
                cmd.CommandText = sql
                Using da As New OleDb.OleDbDataAdapter(cmd)
                    If RecordsToFetch > 0 Then
                        da.Fill(StartFrom, RecordsToFetch, dtResult)
                    Else
                        da.Fill(dtResult)
                    End If
                End Using
            End Using
        End Using
    Catch ex As Exception
    End Try
    Return dtResult
End Function

The above codes will return 10 rows from the table Customer each time the loop operate till the end of file.

Tartuffe

This is the solution I was looking for --> use the fill parameters on da to only fetch what I needed – Incrassate

Answer 5

One easy way to use limit or get pagination working in access is to use ADODB library which support pagination for many DBs with same syntax. http://phplens.com/lens/adodb/docs-adodb.htm#ex8 Its easy to modify/override pager class to fetch required number of rows in array format then.

Bowshot

Answer 6

SELECT  *
FROM BS_FOTOS AS TBL1
WHERE ((((select COUNT(ID) AS DD FROM BS_FOTOS AS TBL2 WHERE TBL2.ID<=TBL1.ID)) BETWEEN  10 AND 15 ));

Its result 10 to 15 records only.

Dalrymple

Answer 7

I felt some of the previous answers were expensive.
A derived table inside a derived table.

I think this syntax does what you need.

SELECT * FROM tblClient ORDER BY Client OFFSET 30 ROWS FETCH NEXT 15 ROWS ONLY

Plasty

This question is about MS Access, your answer is not Access SQL. – Georgiegeorgina