?
Solved

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

Posted on 2011-02-24
3
Medium Priority
?
308 Views
Last Modified: 2012-05-11
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)
OR
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.

Thanks,

OS
0
Comment
Question by:onesegun
3 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 34969990
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.
0
 

Expert Comment

by:TownTalk
ID: 34971036
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:

http://support.microsoft.com/kb/219151
0
 

Author Comment

by:onesegun
ID: 35016200
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.

Thanks,

OS

TownTalk:
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....
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question