Ms Access Limit

how to use LIMIT in query of MS ACCESS 2007

There is no LIMIT keyword in Access (if you use the JET engine). You can use TOP x to give the first x results.
Usage:

SELECT TOP 5 id FROM users ORDER BY joindate

From Microsoft Jet Database Engine Programmer's Guide - Chapter 4:

TOP N and TOP N PERCENT Predicates

Although you can use the WHERE and HAVING clauses to filter the selection of records, sometimes this isn't sufficient. For example, you may want to select all records where the state is CA, but only see the orders for the top 10 customers. Microsoft Jet provides TOP N and TOP N PERCENT predicates to limit the presentation of records after they're selected.

TOP N Predicate

You can use the TOP N predicate to specify that your query return only a specific number of records to your program:

How to deal with Databases which exceed 2gb

When faced with the inherent 2GB limit on the size of your MS Access database, there are several steps that you can undertake, depending on how aggressively you need to reduce the size of the database:

Step 0: Compact the Database

The obvious first step, but I'll mention it here in case it has been overlooked.

Step 1: Splitting the Database

The operation of splitting the database will separate the 'front end' data (queries, reports, forms, macros etc.) from the 'back end' data (the tables).

To do this, go to Database Tools > Move Data > Access Database

This operation will export all tables in your current database to a separate .accdb database file, and will then link the tables from the new .accdb database file back into the original database.

As a result of this operation, The size of the back end database will be marginally reduced since it no longer contains the definitions of the various front end objects, along with resources such as images which may have been used on reports/forms and which may have contributed more towards the overall size of the original database.

But since the vast majority of the data within the file will be stored in the database tables, you will only see marginal gains in database size following this operation.

If this initial step does not significantly reduce the size of the back end database below the 2GB limit, the next step might be:

Step 2: Dividing the Backend Database

The in-built operation offered by MS Access to split the database into a separate frontend and backend database will export all tables from the original database into a single backend database file, and will then relink such tables into the front end database file.

However, if the resulting backend database is still approaching the 2GB limit, you can divide the backend database further into separate smaller chunks - each with its own 2GB limit.

To do this, export the larger tables from your backend database into a separate .accdb database file, and then link this new separate database file to your frontend database in place of the original table.

Taking this process to the limit would result in each table residing within its own separate .accdb database file.

Step 3: Dividing Table Data

This is the very last resort and the feasibility of this step will depend on the type of data you are working with.

If you are operating will dated data, you might consider exporting all data dated prior to a specific cutoff date into a separate table within a separate .accdb database file, and then link the two separate tables into your frontend database (such that you have a 'live' table and an 'archive' table).

Note however that you will not be able to union the data from the two tables within a single query, as the 2GB MS Access limit applies to the amount of data that MS Access is able to manipulate within the single .accdb file, not just the data which may be stored in the tables.

Step 4: Migrate to another RDBMS

If you're frequently hitting the 2GB limit imposed by an MS Access database and find yourself sabotaging the functionality of your database as a result of having to dice up the data into ever smaller chunks, consider opting for a more heavyweight database management system, such as SQL Server, for example.

how to use LIMIT in query of MS ACCESS

Access does not support OFFSET, so what you can do is a trick like this:

select top 10 * 
from tablename
where id > (select max(id) from (select top 30 id from tablename order by id ))
order by id

it will return the rows from 31st to 40th ordered by id.

Note: never use TOP or LIMIT in sql without ORDER BY because the result is not guaranteed to be what you expect.

If you want that id_temp column, you can get it like this:

SELECT 
  ((select count(*) from tablename where id < t.id)+1) AS id_temp, 
  t.*
FROM tablename AS t
ORDER BY t.id;

Access 2010 Limit Query Results

What then is the Access equivalent of MySQL: LIMIT 1001, 25000 (ie return 25,000 results starting from the 1,001st)?

Unfortunately, in MS Access this isn't as straightforward as in MySQL.

In Access, you need to work with nested subqueries.

Here' an answer of mine where I'm showing how to build the correct SQL string for paging in C#:

How to do MS Access database paging + search?

Taking the SQL string from that answer and inserting your table name and column names will result in this query:

select [Cost Centre Code] from tblGL
where [Cost Centre Code] in (
    select top 25000 sub.[Cost Centre Code]
    from (
        select top 26000 tab.[Cost Centre Code]
        from tblGL tab
        where 1=1
        order by tab.[Cost Centre Code]
    ) sub
    order by sub.[Cost Centre Code] desc
)
order by [Cost Centre Code]

This eliminates at least the need for basic C# knowledge, but I'm afraid you'll still be confused in case you don't know how subqueries work :-)

The problem is:

Access has no built-in way to directly get 25000 rows, but skip the first 1000.

The only thing that's possible is to get the first X rows.

So I'm doing this (from the inside to the outside):

1. Load the first 26000 rows
   
   (1000 + 25000, because we want to skip 1000 rows and then load 25000)

2. From this dataset, load the first 25000 rows, but order descending.
   
   This will effectively load row 26000 to 1001 (in that order, because we ordered the rows descending!)

3. To get the rows in ascending order, just load from the table again (and order ascending!), but only the rows with the Cost Centre Codes from step 2.

Got it?

Yes, it looks intimidating at first glance, but as soon as you "get" subqueries, it's actually not that difficult.

MS Access LIMIT X, Y

While the Access/JET TOP keyword does not directly provide an OFFSET capability, we can use a clever combination of TOP, a subquery, and a "derived table" to obtain the same result.

Here is an example for getting the 10 rows starting from offset 20 in a Person table in ORDER BY Name and Id...

SELECT Person.*
FROM Person
WHERE Person.Id In 
      (
        SELECT TOP 10 A.Id
        FROM [
               SELECT TOP 30 Person.Name, Person.Id
               FROM Person
               ORDER BY Person.Name, Person.Id
             ]. AS A
        ORDER BY A.Name DESC, A.Id DESC
      )
ORDER BY Person.Name, Person.Id;

Essentially, we query the top 30, reverse the order, query the top 10, and then select the rows from the table that match, sorting in forward order again. This should be fairly efficient, assuming the Id is the PRIMARY KEY, and there is an index on Name. It might be that a specific covering index on Name, Id (rather than one on just Name) would be needed for best performance, but I think that indexes implicitly cover the PRIMARY KEY.

Access Database LIMIT keyword

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

---

Related Topics