Solved

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

Posted on 2011-02-24
3
299 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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

861 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now