473,287 Members | 1,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Export large query from Access 2007 to Excel 2007

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
Nov 20 '08 #1
4 22998
Stewart Ross
2,545 Expert Mod 2GB
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.
Nov 29 '08 #2
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
Feb 27 '09 #3
@b4iquit
Use
DoCmd.TransferSpreadsheet acExport, 10, etc.
Apr 28 '10 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

14
by: bonehead | last post by:
Greetings, I'm using the DoCmd.TransferText method to export the results of a MS Access query to a csv file. The csv will then be used to load an Oracle table. In other systems such as TOAD...
3
by: excyauseme | last post by:
Hi guys! Do you know what is the best way to export a text file, this one is a log file that is already comma delimited thru a module run by my access database, to an excel spreadsheet? I need to...
5
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
10
Nathan H
by: Nathan H | last post by:
I am curious if this is possible to do. I have a case scenario where I need to export data from an access table into an excel spreadsheet, and have that data start on row 5. Can't do it from Excel...
2
by: Access user | last post by:
My apologies for crossposting this, but I did not get any response in microsoft.public.access ...
1
by: mei2523 | last post by:
I've got a query that contains a field called "URL", that field is a hyperlink field. I've got these super long hyperlinks in this field for different records, and for some reason, when I export...
2
by: mkbrady | last post by:
I have a query that includes calculated fields that generate numeric results. I have wrapped conversion functions CLng() and CDdl() around the calculated fields to ensure the data types are...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.