Data transfer from Access 2007 to Excel 2007 stops at 65,xxx rows

10 pts.
Tags:
Data transfer
Data Transfer Function
Excel 2007
Microsoft Access 2007
Microsoft Excel database
How do we transfer/export data with over 575,000 rows in Microsoft Access 2007 to Microsoft Excel 2007? Is there a way to change the export data # of rows restriction of 65,xxx rows to 1 million rows in Microsoft Access 2007?

Software/Hardware used:
Microsoft Access 2007 and Microsoft Excel 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

the short answer is “no, you cannot make this a million row spreadsheets.”

you have to make multiple spreadsheets in the book. in this way, you can use a counter to find the magic number of 65,536 records and then start a new spreadsheet each time the counter hits this number.

then in the last spreadsheet (or the first, once you figure it out) you can have links that show the totals for each spreadsheet and then the grand total(s).

if however you want to transfer something like your database, then you have to follow the same formulat for writing to and from the spreadsheet(s) in the workbook.

-sarge

————

Excel 2007 only support 65536 rows per sheet, which is the problem that you are hitting. To put more rows than that into a single Excel sheet you would need to upgrade to Excel 2010 (as well as the rest of office probably).

————
Hi, i have installed xl 2010 with 1 million rows but when i download from AS400 then data stop at 65,xxx
Please advised

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Excel 2007 should allow up to 1,000,000 rows. See Excel specifications and limits and also The "Big Grid" and Increased Limits in Excel 2007. As far as Access goes, though, it shouldn't be used for tables that size. (Nor should Excel be used for spreadsheets that size, but Access has SQL Server or other DBMSs as a solution for large tables.) That's part of the point of the relative cost of Access. Tom
    125,585 pointsBadges:
    report
  • SbElectric
    Yes, Excel 2007 can handle 2^20 (a little over 1,000,000) rows and 2^14 (16,384) columns. For Access 2007 the limit is addressable table size space -- 1 gb and sometimes 2 gb. So with less record size, the number of rows could be quite large. As TomLiotta has rightly suggested migrating to SQL sever (or other DBMS) is a better solution, But I have seen Access 2007 application with large number of rows performing simple queries, address mail/merge application for label printing or keeping track of parts for inventory purpose. If the organization does not have SQL expertise then they can survive on Access 2007.
    2,540 pointsBadges:
    report
  • TomLiotta
    I've seen large Access tables as well, but I'm always concerned about unanticipated limits. If I was going to approach sizes like half a million rows, and expect interchanges with other apps, I'd probably just go to SQL Server Express (SQL Server Desktop Engine or whatever the current appropriate name is). Earlier MSDE installs could be pretty handy, though I haven't used any of it for a number of years. I just realized that and downloaded the later SQL Server DE SP3 to this WinXP PC to see what comes out of it. If it's interesting, I might get a chance to add comments here. It wasn't very useful back then for Access users because of the different interfaces. I've heard a few good things since then Tom
    125,585 pointsBadges:
    report
  • carlosdl
    This is an excerpt of the "Excel specifications and limits" page whose link was posted by Tom. Excel specifications and limits Applies to: Microsoft Office Excel 2007
    Feature  	Maximum limit
    
    Open workbooks 	Limited by available memory and system resources
    Worksheet size 	1,048,576 rows by 16,384 columns
    According to this, the limit IS NOT 65536 rows, but 1,048,576
    70,200 pointsBadges:
    report
  • graybeard52
    >>> Hi, i have installed xl 2010 with 1 million rows but when i download from AS400 then data stop at 65,xxx <<<< I expect the problem is the "AS/400" is downloading to Excel 2003 format that has the limit. Not sure what version of Client Access you are running. You could possibly work around this by downloading a CSV file or using RPG to create the XLS file.
    3,115 pointsBadges:
    report
  • Stiloabarthian
    I performed a file transfer using iseries access download, selected BIFF8 as the format and it loaded the first 65000 rows into sheet one and the rest into sheet2 (Excel 2010). This created the older format .xls. The file was saved as .xlsx and the two sheets were then combined.
    175 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following