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

Datestamp

If you intend using pagination for the purpose of producing reports then you would be better off using ms-access's report generating functionality. – Lonnylonslesaunier

I would say that if you need this, you're using the wrong database engine because the environments in which this is a properly useful feature are environments in which Jet/ACE is mostly inappropriate. – Lactone

Answer 1

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.

Colonel

Answer 2

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.

Cerium

aww. That would work if I didn't have a joined table. Thanks anyway – Datestamp

Yeah, it sucks. Oracle has ROWNUM; MySQL has LIMIT X, Y; Access has nothing to help. – Cerium

Even if ID's are not sequential, couldn't one use TOP chunksize WHERE id > max_id_of_previous_page ORDER BY id? – Foramen

Answer 3

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.

Wield

Answer 4

Only way to achive paging SQL similar to Limit statement by using TOP keywords is as follows:

First Step:

sql = "select top "&LESS_COUNT&" * from (SELECT top "&(PAGE_COUNT*getPage)&" * FROM (SELECT "&COLUMNS&" FROM "&TABLENAME&") AS TBL "&getWhere&getOrderby("asc")&") as TBL "&getOrderby("desc")

Second step:

sql = "SELECT TOP "&PAGE_COUNT&" * FROM (" & sql & ") as TBL "&getOrderby("asc")

To summarize; you should re-order and make the results upside down for 3 times.

Euratom

Answer 5

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.

Vary

You are completely insane if you think MS's documentation is inferior to PHP and MySQL. – Lactone

You have clearly never actually used MS's documentation. As someone who has extensively used both MS's documentation and the documentation for PHP and MySQL, there is no contest -- MS wins hands down. – Lactone

As regards the Access Database Engine, the state of the documentation is bad and has never been particularly good. Is it worse than mySQL? Yes and here's why: mySQL aspires to conform with SQL Standards and has achieved a good deal of compliance. Crucially, its documentation details convergence (and divergence) details. The advantage of this is that the ANSI/ISO specs are very detailed, so if a feature is declared as compliant, we already have a high level of detail. The Access Database Engine completely lacks this level of detail and always has :( – Wield

...SQL Server's is quite good, IMO. For example, consider this: "Precision, Scale, and Length (Transact-SQL)" (msdn.microsoft.com/en-us/library/ms190476.aspx) Good, practical detail, methinks. You certainly won't find anything like that level of detail for the Access Database Engine's DECIMAL data type. Users are left to figure it out for themselves by trial and error, usually unwittingly! – Wield

Documentation of Jet/ACE was not mentioned, only the documentation for Access. – Lactone

@David W. Fenton -- Well how can you compare mySQL (a SQL DBMS) with your definition of Access (a RAD environment). Everyone else's definition has Jet/ACE as a subset of Access. What does the 'A' in 'ACE' stand for...? – Wield

...so can I take it that you agree with me about the poor state of the Jet/ACE, then? ;) – Wield

This 'answer' doesn't in any way answer the OPs question. – Burkhart