Limit the number of records affected by an Access query

Question

Is it possible to retrive record with limit in MS-Access Database table?

I have to use update query; while using this I am getting a syntax error.

Example:

Update Report Set Status='Processing' where Status='No' LIMIT 10

Can any one help me on this?

Answer 1

MS Access doesn't have a LIMIT field, but you can use this:

Update Report 
Set Status='Processing' 
where ID IN (
    SELECT TOP 10 ID 
    FROM Report WHERE Status='No' 
    ORDER BY ID
)

Just replace ID with your primary key, and this will update the top 10 records.

Now if you want to get something equivalent to LIMIT(10,20), it gets a little more complicated...

Answer 2

In addition to LittleBobbyTables' answer (which is correct), here's an example how to do LIMIT(10,20) in Access SQL:

How to do MS Access database paging + search?