Importing Access query into Excel

I will try to explain my situation since it would be difficult to provide the actual excel file due to the security reasons pertaining to the data, and without that data the excel/access files are useless. I've provided an example to try to illustrate this, I get too wordy sometimes and can confuse the point I think. My wife says since I'm an engineer and a man, I'm already at 2 strikes in the communication department.

So here goes....
I am using both Access 2003 and Excel 2003.

I can currently accomplish the task; however, it is incredibly slow for just a small amount of data, and I am expecting at least 10 times the data, so the analysis could take an extreme amount of time, and the main purpose of this program is to decrease time of the user having to copy/paste the data manually and risk inaccuracies.

I am programming an Excel spreadsheet to do some analysis of quantities of parts and how long a part has been at a supplier for repair. I import the raw Excel data file that is provided by the user into Access, it then goes through various queries to obtain various types of data. It then will be exported back to the Excel spreadsheet that I am creating for presentation and creating charts within excel.

I have no issues with analyzing all the data, and exporting it to Excel from Access for the most part; however, there is a need to narrow the data down based upon a date range. So the user would only be interested in seeing the data from 5/27/2009 to 6/7/2009, for example.

I create a query to narrow down the data from the table. For each part number and part serial number, there may be multiple entries, due to the fact the raw data file that is submitted contains all past history of the part. So I have created other queries to sort out the most current data based on the most recent date. In the last query, I have combined all the queries to show the final, and most current data in the file for each part.

The user wants to create a graph in Excel of a daily average of the number of days each part has been out for repair within the specified date range. This is being done to show performance improvements over time. So to create the chart I have Cxcel list each day within the user-specified date range. I then use a QueryDef to narrow the data in the main Access query based upon the date range which would change day to day. The Excel VBA code then uses the ADO copyfromrecordset method to get the average of that day and lists it next to that day in the Excel list of each day in the date range.

For example:

The querydef would redefine the query criteria to use 5/27/2009 as the start date, and 5/27/2009 as the end date. So days the Part 1 has been in repair would be 0, so the average days in repair is 0 (only dealing with 1 part). The final average is sent to Excel via CopyfromRecordSet method of the final query.

The querydef would redefine the query criteria to use 5/27/2009 as the start date, and 5/28/2009 as the end date. So the next day, 5/28/2009 and the days Part 1 has been in repair is 1, so the average days in repair is 1 (only dealing with 1 part). The final average is sent to Excel via CopyfromRecordSet method of the final query.

The querydef would redefine the query criteria to use 5/27/2009 as the start date, and 5/29/2009 as the end date. So the next day, 5/29/2009 and the days Part 1 has been in repair is 2. Now Part 2 has entered repair so the number of days Part 2 has been in repair is 0, so the total average days in repair for both parts is 1 day (dealing with 2 parts). The final average is sent to Excel via CopyfromRecordSet method of the final query.

The querydef would redefine the query criteria to use 5/27/2009 as the start date, and 5/30/2009 as the end date. So the next day, 5/30/2009 and the days Part 1 has been in repair is 3. the number of days Part 2 has been in repair is 1, so the total average days in repair for both parts is 2 (dealing with 2 parts). The final average is sent to Excel via CopyfromRecordSet method of the final query.

The querydef would redefine the query criteria to use 5/27/2009 as the start date, and 5/31/2009 as the end date. So the next day, 5/31/2009 and the days Part 1 has been in repair is 3. the number of days Part 2 has been in repair is 2, so the total average days in repair for both parts is 2.5 (dealing with 2 parts). The querydef would redefine the query criteria to use 5/27/2009 as the start date, and 5/31/2009 as the end date. The final average is sent to Excel via CopyfromRecordSet method of the final query.

This continues until Excel has looped through each day in the date range. More parts could be entering the calculations on any given day.

I've used days instead of months/quarters since it would be the most extreme case, and the user wants the option of searching by days/weeks/month/quarter.

The final list within Excel would look something like this:

DATE
5/27/2009 (start date)
5/28/2009
5/29/2009
5/30/2009
5/31/2009
......
6/7/2009 (end date)


Average
0
1
1
2
2.5
......
5

These 2 lists would obviously be side by side in Excel, but I fail at formatting on here so I just listed them like this for ease.

Excel will then use this list to as part of multi-data chart.

As I said, what i've coded works, but is slow. For 14 lines of data, I've timed it at around 51 seconds. I'm expecting to have the program analyze many more lines as time goes on, so this could take a while, and I want to save as much time as possible for the user to focus on analysis of the information, not waiting for it to be presented.

Is there another way I can speed this up? I've tried just using the CopyfromRecordSet method on my first Query, defining the date range on the fly. However, that factors in all the data history from each part, which is not what I want. I want the most current date data for the part based upon the end date the user specifies, so it has to run through 2 other queries, and using the CopyfromRecordSet method on them won't work because they are filtering certain aspects of the data, and then recombining them in the final query, which is what is used to export the average back into Excel.

I know this is wordy, please feel free to ask questions, as I said, providing the file is not an option due to security, and I could provide code, but the code works and I'm only looking for a time saving solution.

Thanks in advance

that is a good possibility, how about running the QueryDef for each day and then appending it to a temp table? Or is there an easier way in Access to specify a large date range and then iterate it one day at a time, without having to loop it in VBA?