As there is no native offset function in ms access, I am using following technique to paginate data taken from this tutorial:http://www.jertix.org/en/blog/programming/implementation-of-sql-pagination-with-ms-access.html
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
--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;
With this technique, for page one, the database has to select all the records inside subquery. Is this technique as efficient as mysql offset and limit. If not I do not think, pagination is at all necessary on the sql side. I could just use some client side pagination probably with some javascript. Is there a better way to paginate in ms access or am I better off without pagination as there is no real advantage over performance?
