Access export to Excel stops at about 45,000 not 65,000 XL limit

[EagleOne@microsoftdiscussiongroups]

Accrss 2003 Excel 2003 all up-to-date

Access export to Excel stops at about 45,000 records. The version of Excel
(2003) will hold 65,000+ records. How do I get around this error?

Access gives this error: Run-time error '2036':
"There are too many rows to output, based on the limitation specified by
the output format or by Microsoft Office Access."

The command used is next:
DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False

 

[Jerry Whittle]

How many records are you trying to export? If more than 64,000, Access can
stop well before it gets to that number.

If you upgrade to Office Pro 2007, Excel can handle 1,000,000 records.

 

[EagleOne@microsoftdiscussiongroups]

Jerry,

The one Table in Access has 42,000 Records

The entire Database (20 tables)

 

[EagleOne@microsoftdiscussiongroups]

I did not finish my info:

The entire database has 20 tables and about 250,000 records in all.

That said, I have issued 20 back-to-back DoCmd.OutputTo acOutputTable .....
for all 20.

FYI four tables made it, but the 5th one with 42,000 failed.

 

[Ken Hudson]

I assume that it is not choking because you reached the 2GB size limit of the
db?

 

[Ken Hudson]

Sorry, never mind.
Access capacity is not in question.

 

[EagleOne]

Ken,

The Access file size is 34MB

I am quite surprised that it choked at 42,000 when XL's limit is 65,000

Is there a parameter missing in my command? i.e., is Access assuming some previous limitation?

EagleOne

 

[Guest]

Which method are you using to export? Which format are you
exporting? The default format may limit at 16000 rows (the very
old Excel limit before Office 97), or may be limited by available
System Resources (Windows).

(david)

"EagleOne@microsoftdiscussiongroups"

 

[Nicholas Meyer]

hmmm
"EagleOne@microsoftdiscussiongroups"

 

[EagleOne]

Actually, I may have solved it.

At home (vs work) I have a slower (1G) and 1/2 the memory (256M) of my work computer
By changing DoCmd.OutputTo ...... to DoCmd.TransferSpreadsheet ....... did the job!

Thanks for all who took time

EagleOne

 

[EagleOne]

Actually, I may have solved it.

At home (vs work) I have a slower (1G) and 1/2 the memory (256M) of my work computer
By changing DoCmd.OutputTo ...... to DoCmd.TransferSpreadsheet ....... did the job!

Thanks for all who took time

EagleOne

 

[Guest]

Yes, I didn't see the bottom half of your message: :~)
acFormatXLS

Excel 5/95. 16000 rows max.

(david)