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

  • Thread starter Thread starter EagleOne@microsoftdiscussiongroups
  • Start date Start date
E

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
 
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.
 
Jerry,

The one Table in Access has 42,000 Records

The entire Database (20 tables)
 
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,

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
 
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"
 
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
 
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
 
Yes, I didn't see the bottom half of your message: :~)
acFormatXLS

Excel 5/95. 16000 rows max.

(david)
 
Back
Top