Excel - Best Way to Connect With Access Data

Here is the situation we have:

a) I have an Access database / application that records a significant amount of data. Significant fields would be hours, # of sales, # of unreturned calls, etc

b) I have an Excel document that connects to the Access database and pulls data in to visualize it

As it stands now, the Excel file has a Refresh button that loads new data. The data is loaded into a large PivotTable. The main 'visual form' then uses VLOOKUP to get the results from the form, based on the related hours.

This operation 开发者_如何学JAVAis slow (~10 seconds) and seems to be redundant and inefficient. Is there a better way to do this?

I am willing to go just about any route - just need directions.

Thanks in advance!

Update: I have confirmed (due to helpful comments/responses) that the problem is with the data loading itself. removing all the VLOOKUPs only took a second or two out of the load time. So, the questions stands as how I can rapidly and reliably get the data without so much time involvement (it loads around 3000 records into the PivotTables).

You need to find out if its the Pivot Table Refresh or the VLOOKUP thats taking the time. (try removing the VLOOKUP to see how long it take just to do the Refresh).

If its the VLOOKUP you can usually speed that up. (see http://www.decisionmodels.com/optspeede.htm for some hints)

If its the Pivot table Refresh then it depends on which method you are using to get the data (Microsoft Query, ADO/DAO, ...) and how much data you are transferring. One way to speed this up is to minimize the amount of data you are reading into the pivot cache by reducing the number of columns and/or predefining a query to subset the rows.