How can I limit Access query to 65000 records

[CarpeDiem]

Hi,

I am trying to link this access query to ms excel spreasheet. Excel has a
limitation of 65000 records. How can I get a running count of records in an
Access query so I would be able to limit the records to first 65000 records
and in later queries records which have a count of more than 65000 to 120000,
then in the third query the later counts. The total number of records are
more than 318,000.

Thanks.

 

[Clifford Bass]

Hi,

You can limit a query by using the top clause.

select top 65000 field1, field2, ...

How to determine where to start for your suceeding queries is going to
be dependent on how you are sorting and what fields in the sort make it
possible to identify the following record uniquely. If you want it entirely
automated, you could probably do some kind of looping in code where you
export the data, then open up the query to see how many rows exported. If
65,000, then look at the last record and use it to create a new temporary
query that starts with that row and is also limited to 65,000. Repeat the
process until less than 65,000 rows have been exported.

Others may have better ideas.

Clifford Bass

 

[a a r o n _ k e m p f]

A) Don't put the data into Excel
B) if you must report on the data in Excel, use an Excel pivotTable
against the database directly
C) or move to Office 2007 which has a 1m record limit (in other words,
Excel 2007 is a better database than _ANY_ version of Jet)

 

[Tony Toews [MVP]]

C) or move to Office 2007 which has a 1m record limit (in other words,
Excel 2007 is a better database than _ANY_ version of Jet)

This statement is wrong for a number of reasons. Jet can easily
handle over a million records in a table. And Excel is not a
database.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n _ k e m p f]

Jet is a piece of shit database.

anyone that calls themselves a professional should learn SQL Server
or.. just move back to Excel.

Excel is more reliable than Jet, I've never seen Excel corrupt data.
Jet corrupts data all the time.

-Aaron