Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 695
  • Last Modified:

Microsoft Access Export Limit

It appears that MS Access 2002 limits the size of an exported table (to Excel) to 65,536 rows.

Is there a way to overcome this?

Thanks
Access-vers.jpg
0
Richard Korts
Asked:
Richard Korts
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
It's not Access, but Excel.   That's the row limit in Excel in 2002.

Jim.
0
 
aikimarkCommented:
If you use DoCmd.TransferSpreadsheet it will only output 64K rows, even if it is a newer version of Access, whose (same Office version) Excel is capable of handling 1M rows.

I recommend instantiating an ADO recordset object, instantiating an Excel automation object and then using the CopyFromRecordset method to transfer the data from Access to Excel.

There are other methods that I show in my article:
Fast Data Push to Excel:  http:A_2253.html
0
 
aikimarkCommented:
@rkorts

Your accepted solution may not be correct.  Although older versions of Excel have the 64K limit, if you need to expert Access2003 rows to an Excel 2007 worksheet, you can get around the Access limitation (not, in this scenario, an Excel limitation).
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now