Export large query from Access 2007 to Excel 2007

[b4iquit]

Hi,
I need to rack and stack some data using a pivottable. I'd like to do it in excel. I have the query in Access 2007 finished but it is 800,000+ records. How can I get it to Excel 2007....it keeps giving me errors related to 65K line limit. Super frustrated....and not highly skilled at Access but on a deadline.
Thanks

[Stewart Ross]

Hi. I realise that this response is too late to help you now - sorry for the lack of response.

Anyway, exporting to Excel is the problem here. Excel has historically had a limit of 64K rows, and that limit is built in to the Access-to-Excel export routines. You could in VBA loop through your data, exporting blocks of rows up to the row limit for any one worksheet, inserting new worksheets and exporting to these as necessary.

Unfortunately this would be of no help at all if you wanted to use an Excel pivottable or any other Excel facility on all rows of your data, as the 800k rows would be split across around 13 worksheets.

Conclusion is that if you want to export more than 64K rows from Access then exporting to Excel workbooks is out.

-Stewart

PS Excel 2007 has a much greater row limit of 1M rows. It would therefore be possible to transfer all of your rows to the one worksheet using VBA code to do so, but clearly the built-in export routines are still limited by the lower row limits of previous versions.

[dmacris]

I am dealing with a database of 4.5 million records. I have a query of about 250,000 that i am trying to export to a text file or excel and the porgram can hardly open the file let alone work with it! What is going on with 2007? Is it a total POS or am I doing something wrong. I should be able to export the data somehow and then import it into excel right?

Let me know

Thanks
Dean

[FishVal]

Exporting huge records to Excel from Access

[newnewbie]

@b4iquit
Use
DoCmd.TransferSpreadsheet acExport, 10, etc.