Access Database LIMIT keyword

Question

I'm trying to get my page listing function working in ASP with an Access database, but I don't know the alternative to LIMIT in Microsoft SQL. I have tried TOP but this doesn't seem to be working.

Here is the statement am using with MySQL:

SELECT  * FROM customers ORDER BY customerName DESC LIMIT 0, 5

How can I convert this to work with Access Database?

Answer 1

There is no direct equivalent in access for LIMIT, but the TOP statement can be manipulated into working in a similar fashion to say, "... LIMIT BY 50, 250" etc,. I found out by experiment that if you wanted to get the "next 50" records at an offset of 250 you could try the following

SELECT * FROM (SELECT TOP 50 tab2.* FROM (SELECT TOP 300 tab1.* FROM my_table AS tab1 ORDER BY column_name ASC) AS tab2 ORDER BY column_name DESC) ORDER BY column_name ASC;

This should return the records from row 250 to 300, in ascending order (provided they exist.) with or without a unique index. A WHERE clause could tidy the results further if need be.

A little convoluted but I hope it helps.

Answer 2

According to ms-access view:

SELECT TOP(5) * FROM customers ORDER BY customerName; 

will fetch an error "The SELECT statement includes a reserved word",

the correct syntax is:

SELECT TOP 5 * FROM customers ORDER BY customerName; 

(note the brackets)..

Answer 3

Top(5) is deceptive. Internally the database returns all records, then Access just shows the Top 5 rows. I'd use the LIMIT keyword instead of Top(n).