Solved

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

Posted on 2011-02-24
3
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 85

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

691 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