Results 1 to 9 of 9
  1. #1
    TonyBender is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    4

    Exporting more than 65000 Access rows to Excel


    I have Office 2007 and am trying to export a table with 500,000 rows of data into an Excel worksheet. However it is not letting me do this.

    Since Excel 2007 has capacity for upwards of 1 million rows why can't I export a huge Access table.

    Please let me know if this can even be done.

    Thank you

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    At what number is it falling over on?

    Are you sure it is Excel that is not accepting the rows or is it Access that cannot process the data. It could also be a network issue in regards to maximum load performance and read/write events.

    David

  3. #3
    TonyBender is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    4

    Exporting more than 65000 Access rows to Excel

    David,

    I don't know, other than at 502,500 records I got an error message telling me the file was more than Excel could handle.

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    This may be due to a memory or caching problem. Remember Access\Excel attempt to retain the contents of the data in the buffer for sake of the undo feature. It may well ber that you have filled this up and it is saying sorry but my buffer is full I wojn't be a ble to undo this command even if you want to.

    To Prove this is the case, create a new query of your original one but reduce the column count to say 3 fields and try and export this. In effect it should be able to export more records to Excel because the actual size of the data block is smaller.

    How many records in total do you want to export?
    Have you tried doing it in stages.

    David

  5. #5
    dsmacs is offline Advanced Beginner
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2009
    Location
    Perth Western Australia
    Posts
    52
    Excel can only accept 65000 rows, you'll need to split the data and import into seperate worksheets or save as csv?

  6. #6
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    I know 2007 allows 1m+ rows in a worksheet, however if the worksheet is being populated by an outside force, such as Access, it may restrict it to 65k rows.

    Here is a link to a MS article on how to load files with more than the allowable limit. May be of help to you.

    http://support.microsoft.com/kb/120596

    David

  7. #7
    TonyBender is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    4

    Exporting more than 65000 Access rows to Excel

    Thanks to all, I appreciate your help

  8. #8
    TheReb is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    3
    I have the same issue. However, it appears that the problem is with Access 2010. I am told that the clipboard can't handle more than 65,000 rows. Any ideas?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Really Need Help With Exporting to Excel
    By graviz in forum Import/Export Data
    Replies: 0
    Last Post: 09-24-2009, 08:29 AM
  2. Access to Excel transferring multiple rows to single row
    By peter_lawton in forum Import/Export Data
    Replies: 10
    Last Post: 09-23-2009, 10:16 AM
  3. Exporting Access queries to Excel
    By dbDamo in forum Import/Export Data
    Replies: 2
    Last Post: 09-22-2009, 01:42 AM
  4. Replies: 0
    Last Post: 06-05-2009, 10:47 AM
  5. Exporting a Report to Excel
    By bullwinkle55423 in forum Reports
    Replies: 0
    Last Post: 12-11-2007, 10:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums