6

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?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Elliott
  • 3,812
  • 24
  • 69
  • 93

3 Answers3

19

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)..

dEePaK
  • 242
  • 2
  • 5
4

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).

xd9813
  • 41
  • 1
3

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.

Haloeye
  • 31
  • 1