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

If you intend using pagination for the purpose of producing reports then you would be better off using ms-access's report generating functionality. — heferav, Oct 14 '09 at 09:26
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. — David-W-Fenton, Oct 15 '09 at 03:46

score 5 · Answer 1 · answered Oct 14 '09 at 03:01

5

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.

answered Oct 14 '09 at 03:01

James Cronen

5,715
2
32
52

aww. That would work if I didn't have a joined table. Thanks anyway – wnoveno Oct 14 '09 at 03:46
Yeah, it sucks. Oracle has ROWNUM; MySQL has LIMIT X, Y; Access has nothing to help. – James Cronen Oct 14 '09 at 04:14
Even if ID's are not sequential, couldn't one use `TOP chunksize WHERE id > max_id_of_previous_page ORDER BY id`? – peterchen Oct 14 '09 at 13:01

score 3 · Answer 2 · answered Oct 14 '09 at 10:24

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.

score 2 · Accepted Answer · answered Oct 14 '09 at 12:56

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.

score 0 · Answer 4 · answered Apr 05 '11 at 01:21

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.

score -3 · Answer 5 · answered Dec 07 '09 at 17:30

-3

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.

answered Dec 07 '09 at 17:30

Kevin Loring

5

You are completely insane if you think MS's documentation is inferior to PHP and MySQL. – David-W-Fenton Dec 08 '09 at 03:49
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. – David-W-Fenton Dec 08 '09 at 03:50
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 :( – onedaywhen Dec 08 '09 at 09:28
...SQL Server's is quite good, IMO. For example, consider this: "Precision, Scale, and Length (Transact-SQL)" (http://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! – onedaywhen Dec 08 '09 at 09:33
Documentation of Jet/ACE was not mentioned, only the documentation for Access. – David-W-Fenton Dec 08 '09 at 22:18
@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...? – onedaywhen Dec 09 '09 at 08:30
...so can I take it that you agree with me about the poor state of the Jet/ACE, then? ;) – onedaywhen Dec 09 '09 at 08:32
This 'answer' doesn't in any way answer the OPs question. – pseudosavant Jun 06 '17 at 23:25

MS Access Limit

5 Answers5

Linked

Related