Export to Excel

[jjaeger]

Hi All,

I am trying to export a query out of Access 2007 and into excel. When I run my export, it continues to run for hours without exporting the file. Any ideas, how I can export to excel or input as to what I am doing wrong. On average, the query returns 30k-60k records so I am dumbfounded as to why it will not export...

thanks for your help!

 

[Norie]

How are you exporting the query?

 

[jjaeger]

I have tried to Export Data -> Excel and I have tried to just copy the query (Ctrl + C). Both ways, the export continues to run for hours...

 

[Trevor G]

Look to use a MACRO (In 2007 you must remember to add All Commands at the Top of the MACRO), you can use TransferSpreadsheet and then in the bottom area you select the following:

Transfer Type (This would be changed to Export)
Spreadsheet Type (Leave alone at this stage)
Table Name (Use the query name)
File Name (Location to be saved to include .xlsx)
Has Field Names (Change to Yes)

 

[jjaeger]

I am not sure what I am doing wrong. I set up my MACRO:
1. Open Query 1
2. Close
3. Open Query 2
4. Close
5. Open Query 3
6. Close
7. Open Query 4
8. Close
9. Open Query 5
10. Close
11. TransferSpreadsheet

My MACRO has beening running for over 3 hour, with my query only taking about 30 minutes. Is this the way to set this MACRO up, do I need to change the order of the 'TransferSpreadsheet'?

This is a process I will have to run on a regular basis and I am just trying to find the most efficient way to get it out of access and into Excel.

Thanks again!

 

[Trevor G]

I would ask what is the process of each query, and why so many in a process?

You shouldn't have to close them after they have run.

If one query is supposed to trigger the next then would it be possible to by pass any of the steps. If you are using a query in another query you don't have to run each separately.

Are you also running everything directly off a network drive. Would you be able to copy the database to a local drive then run it.!

 

[Joe4]

Are you exporting the results from a query?
If you are dealing with a large data set that is not indexed, it can greatly slow down the processes. And depending on what you are doing in the queries, even if you have indexes in the underlying tables, it could be problematic.

One thing I have done in the past is to write the results of the final query you wish to export to a pre-defined table that has indexes set (using an Append Query). Then export from that table instead of the query. (I clean out this table before each run with a Delete Query).

 

[boblarson]

If those queries are only SELECT queries, you do not need to open them before exporting. If they are action queries (Append, Update, or Delete) then that would be different. But if they are just pulling data, you don't open them first.

 

[jjaeger]

Thanks for all the feedback, this has been very informative. I am a rookie when it comes to Access so I am learning a lot from all the great feedback. Just a few follow ups.

- I have ODBC links so I think this is why the process is very slow! Not sure if I can do something to speed up my ability to hit the tables, I am assuming not...

- All my queries are 'SELECT' queries.

- When I run my MACRO, would it be best to:
1. Open Query 6
2. TransferSpreadsheet

- Last, can someone explain 'Indexed data' and how I might be able to identify if my tables are 'indexed'

thanks again!

 

[boblarson]

You don't need to open ANY of the queries. Just use the Transfer Spreadsheet. It will run any queries that make up the query you are exporting.