 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Access export to Excel stops at about 45,000 not 65,000 XL limit
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.  SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > Accrss 2003 Excel 2003 all up-to-date > [quoted text clipped - 7 lines] > The command used is next: > DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False Jerry, The one Table in Access has 42,000 Records The entire Database (20 tables) > How many records are you trying to export? If more than 64,000, Access can > stop well before it gets to that number. [quoted text clipped - 12 lines] > > The command used is next: > > DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False 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. > How many records are you trying to export? If more than 64,000, Access can > stop well before it gets to that number. [quoted text clipped - 12 lines] > > The command used is next: > > DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False I assume that it is not choking because you reached the 2GB size limit of the db? SignatureKen Hudson > Accrss 2003 Excel 2003 all up-to-date > [quoted text clipped - 7 lines] > The command used is next: > DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False Sorry, never mind. Access capacity is not in question. SignatureKen Hudson > I assume that it is not choking because you reached the 2GB size limit of the > db? [quoted text clipped - 10 lines] > > The command used is next: > > DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False 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 >I assume that it is not choking because you reached the 2GB size limit of the >db? 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) > Accrss 2003 Excel 2003 all up-to-date > [quoted text clipped - 7 lines] > The command used is next: > DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False 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 >Which method are you using to export? Which format are you >exporting? The default format may limit at 16000 rows (the very [quoted text clipped - 15 lines] >> The command used is next: >> DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False 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 >Which method are you using to export? Which format are you >exporting? The default format may limit at 16000 rows (the very [quoted text clipped - 15 lines] >> The command used is next: >> DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False Yes, I didn't see the bottom half of your message: :~) acFormatXLS Excel 5/95. 16000 rows max. (david) > Actually, I may have solved it. > [quoted text clipped - 24 lines] > >> The command used is next: > >> DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False hmmm > Accrss 2003 Excel 2003 all up-to-date > [quoted text clipped - 8 lines] > The command used is next: > DoCmd.OutputTo acOutputTable, "PEN_TBL", acFormatXLS, "PEN.xls", False |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.