?
Solved

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

Posted on 2011-02-24
3
Medium Priority
?
304 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 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 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