MSAccess reports into Excel - Version issue?

[johncon]

We have an MSAccess reporting system with preset queuries and macros that dump reports and format them into Excel. I have a user who is claiming that the report when processing into Excel 2000, takes about 20 minutes in total on his machine. However, when I run the same report on my machine, Dell Intel 2 Core Laptop, 2 gb Ram, Excel 2002, it takes about 4 hours to download and format the data as it goes. I believe the user's machine is similar in specs.

Is it possible that MSAccess 2003 is more efficient in dumping info into Excel 2000 vs 2002?

I'm not sure where else to look as the reports are really time consuming and I need to verify all bottlenecks in an attempt to speed them up before I die a slow agonizing report viewing death.

 

[JP2112]

If you post your code, someone here might be able to see if there is anything there that might be causing the issue you're experiencing.

--JP

We have an MSAccess reporting system with preset queuries and macros that dump reports and format them into Excel. I have a user who is claiming that the report when processing into Excel 2000, takes about 20 minutes in total on his machine. However, when I run the same report on my machine, Dell Intel 2 Core Laptop, 2 gb Ram, Excel 2002, it takes about 4 hours to download and format the data as it goes. I believe the user's machine is similar in specs.

Is it possible that MSAccess 2003 is more efficient in dumping info into Excel 2000 vs 2002?

I'm not sure where else to look as the reports are really time consuming and I need to verify all bottlenecks in an attempt to speed them up before I die a slow agonizing report viewing death.

 

[Richard Schollar]

Assuming that you are using Automation to generate the Excel reports (ie there is code in there which instantiates a new instance of Excel from Access) then it is possible that xl2000 would be fatser than later xl versions - this is because the newly instantiated version of Excel occupies a different memory address from the client program (in this case Access) and any information sent between the client and the server (Excel) needs to be copied from the Client memory space to the server memory space. Given that xl2000 is a whole lot simpler than xl2002 - and thus has a smaller memory footprint - I guess it could mean there is less complexity when copying the information over and executing it within the xl server. It would be interesting to see if you can have xl2000 installed on your machine and then see if you experience a similar improvement in performance.


EDIT: The following is anecdotal, but in my last role I wrote some code that dumped out data from Access2002 into an Excel2002 file and performed some formatting on the data. Despite the file being very simple indeed (3 sheets) with no complex formatting, the process took what i considered to be a long time (it was the order of 15 to 20 seconds for really simple data/formatting). So it may be that xl2002 just makes a really rubbish automation server?!