Best way to link between Excel and Access (Excel as Dashboard)

Hi Experts,

I'm creating a report which presents the data in Excel 2007 (mostly by pivot tables) which are linked to various Access queries and tables.

I was wondering, in terms of speed, memory and performance of the Excel reports:
1) Is it better to run off all queries in Access and just connect Excel to Access tables (or queries)
2)Is it better to bring everything into Excel and then amend the connection string in Excel and/or filter via pivot table filters.

Essentially what I'm doing is producing the same data/MI but cutting it for different users hence the filters, queries etc.


Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Are the two files (the Excel file and the Access file) on the same machine? If so, then you probably wouldn't be able to notice any real difference in the performance.

If they aren't, then almost certainly you would have better performance with the data directly in Excel.
I use Automation to exchange data between Access and Excel. it all depends on your coding skills.....

For instance,  In Access you could open a recordset  based on Query of some description, and then in VBA loop through the records in the recordset, and by using automation write the values directly into cells in an Excel workbook.

This kb article gives you an idea of how to get started with automation:
onesegunAuthor Commented:
Hi LSMConsulting,

Yes the two files are on the same machine. In terms of nanagebility is probably easier for me to filter at Excel to make the different cuts.



I somewhat agree with what you are saying but it is far easier just to pivot the data I have which are quite complex. That will take an enoumous amount of time and effort away from coding. If they way simple data sets I would follow your route....
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.