MS Access Limit

Question

What's the equivalent of mysql Limit in ms access. TOP is not sufficient since I'm going to use it for pagination.

Thanks

Answer 1

There isn't one. Your best bet is to add an ID column as a primary key (if you don't already have one) and chunk output by looping through:

SELECT * FROM table
 WHERE id >= offset AND id <= offset + chunk_size - 1

until you get all the rows.

Answer 2

Curiously, there are a few references in Microsoft documentation to a LIMIT TO nn ROWS syntax for the Access Database Engine:

ACC2002: Setting ANSI 92 Compatibility in a Database Does Not Allow DISTINCT Keyword in Aggregate Functions

About ANSI SQL query mode (MDB)

However, actual testing seems to confirm that this syntax has never existed in a release version of the Access Database Engine. Perhaps this is one of those features that the SQL Server team wanted to put into Jet 4.0 but were ordered to rollback by the Windows team? Whatever, it seem we must simply put it down to a bad documentation error that Microsoft won't take the time to correct :(

If you need to do pagination on the server** side then I suggest you consider a more capable, modern SQL product with better documentation ;)

** conceptually, that is: the Access Database Engine is not a server DBMS.

Answer 3

Since it doesn't appear that you have any type of sequencial unique key number for these rows, you'll need to create a ranking column: How to Rank Records Within a Query

You need to determine how many rows at a time you will return N = (10, 25,100).

You need to keep track of what "page" the user is on and the values of the first and last rank.

Then when you make the call for the next page it is either the next N rows that are > or < the first and last ranks (depending if the users is going to the previous or next page.).

I'm sure there is a way to calculate the last page, first page, etc.

Answer 4

port your project to PHP & MySQL. Better support for these type of actions and queries and much much better online documentation. As a 16 year veteran DB developer, I have grown to dispise MS Access and MS SQL with a passion unmatched by anything else. This is due exclusively to their lack of support and documentation.