Why does MSAccess cut query after 2886000 records

We have a large database (Access 2007 on XP) with a data table containing
over 10 million records. We have a series of queries and observed that one
query failed to return all records - It stopped after returning exactly
2,886,000 records. Rerunning the query produced results from all 10 million
records.

We made a copy of the database and issued the same set of queries.
Sometimes the set of queries produces the desired results from all data
records, while other times one (not always the same query) will truncate
after processing exactly 2886000 records.

Why does this happen and what can be done to guarentee good results.

With that many records, you just might be hitting the upper limit of Access.

How big is the database file? Have you done a compact and repair lately?

Do you do any sorting in the query? Next time it happens, remove the sorts
and see if it return all the records.

Maybe post the SQL statements of some of the queries that are having
problems. There might be a better way of running them.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"PhilK" wrote:

We have a large database (Access 2007 on XP) with a data table containing
over 10 million records. We have a series of queries and observed that one
query failed to return all records - It stopped after returning exactly
2,886,000 records. Rerunning the query produced results from all 10 million
records.

We made a copy of the database and issued the same set of queries.
Sometimes the set of queries produces the desired results from all data
records, while other times one (not always the same query) will truncate
after processing exactly 2886000 records.

Why does this happen and what can be done to guarentee good results.