We help IT Professionals succeed at work.

Updating pivot chart links to database

414 Views
Last Modified: 2013-12-25
Hi.
I have a large Excel spreadsheet at our company, which contains a lot of pivot chart reports linked to an Access database. It also has graphs linked in turn to these reports.
What I would like to know is, how can I easily update the links to the access database mdb file on ALL reports.
I assume this would need to be done by VBA if possible.
I may have done this wrong, because I can't seem to see how anyone would be able to update a huge number of links to pivot charts should the location or name of the database change!

Also, there are lots of settings under "Table Settings" that I am not sure about.
Could somebody give me some recommendations for the best settings for a large pivot table Excel file.
Data Source options:
-Save data with table layout
-Enable drill to details
-Refresh on open
-Refresh every x minutes
External data options:
-Save password
-Background query
-Optimize memory

If there is a better way to link a pivot table to an Access database that can easily be updated across multiple reports I would like some comments on it please.
One idea I had was to create a "System" odbc DSN connection, and link to that instead of direct to the database. Would this work, and would it need to be created on all machines that use the Excel file identically?

Thanks in advance.
Comment
Watch Question

I would recommend not to use Pivot tables linked to Access in your situation, but rather query the Access database (Crosstab Query perhaps) and bring the results to Excel for display.

Leon

Author

Commented:
Thans for that Leon.

Just to clarify, what method do you recommend to pull the data into Excel?
I have use the "Data - Import External Data - New Database Query" method before, but this still ties the
Excel sheet to a fixed data source, obviously will be the same issue for all of the reports.

Author

Commented:
Here is an example of 2 of the queries from my Excel sheet.
These are repeated in multiple pivot table reports (probably wrongly!) for each year that we require
data in the report.
I need the criteria of the query to return all records from current year, and the last 4.
Current year:
SELECT *
FROM Projects Projects
WHERE Projects.`Enquiry Date` Between Dateserial(Year(Now()),1,1) And DateSerial(Year(Now()),12,31) AND Projects.[Enquiry Type]='OEM';
4 Years ago:
SELECT *
FROM Projects Projects
WHERE (Year(Projects.[Enquiry Date])=year(now())-4 ) AND Projects.[Enquiry Type]='OEM';

Could you give me an example of a crosstab query to do this job then if this is the way to go?
The queries above are straight forward and do not need to be made into Crosstab queries. What do your Pivot Tables display exactly? Do they do something else, beyond what those Select statements show?

What I am suggesting is to replace the way you bring data into Excel to begin with by using ADO instead of  "Data - Import External Data - New Database Query"

Leon

Author

Commented:
Right I think I understand. So using ADO in a VBA module then is the recommended way?
That way I could use a common variable to hold the database name etc.

The queries I posted are just examples of the many queries I need to perform to get the end results I need, which
is a set of queries returning data for the last 5 years (current year, current year-1 and so on).

Not only could you use the variables to hold database names, but you would be able to concatenate the SQL queries based on the information required. You would also load data on demand as opposed to updating all data linked to the spreadsheet. Your performance should be greatly increased. I suggest you start with one to get a feel of what will need to be done and if it works for you, continue replacing afterwards.

How familiar are you with VBA and ADO?

Leon

Author

Commented:
Ok thanks.
I used to use ADO quite a bit, but not for some time.

Maybe a small example would help refresh my memory? :-)
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks a lot. Will try this when I get around to it, but looks fine and should help me get it done.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.