How do I refresh Excel 2007 pivot tables from the results of an Access query?

I use Excel 2007 to provide detailed reporting and analysis in pivot tables. All my data is stored in Access tables.  Generally, I
1)  run Access queries and export to Excel files;
2)  manually open Excel template (.xltx) files;
3)  manually change the data source of each pivot table one at a time to the new records in the exported (from Access) Excel file.

 I know there has to be a better way!  Any suggestions are appreciated.
Could someone please point me in the right direction?  How should I be thinking differently about this approach?
thutchinsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nutschCommented:
Establish a data query to "access" your Access queries,
Use that as source for your pivot.

office.microsoft.com has this detailed tutorial.

Thomas
0
thutchinsonAuthor Commented:
Thanks, nutsch.  This looks what I need.  I read through the tutorial but I will need time to play a bit with it.
I want the process to be initiated from Access forms. Since the queries are already written in Access should I change the queries to Make Table in Access instead of exporting to Excel?  That way I can connect to the Access table with the data I want to use?

In other words, do I want to create a procedure in Access that will:
1) Run query and make a table (after deleting existing table);
2) Open Excel reporting file; then
3) Refresh data in Excel pivot tables?

Thanks for your help.  Good stuff!

0
nutschCommented:
I don't think you need that, if your query is a select query, you can use it as the base of your pivot. Then the steps are
1) Open Excel
2) Refresh Pivot table.

Done.

T

PS found online:
In Excel, on the Data tab, click From Access
Select your database, click Open
Select the query, click OK
Select PivotTable report, click OK
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

thutchinsonAuthor Commented:
Yes, but the query has to run using parameters supplied by the user through an Access form.
I'm not sure if I'm missing something here...
0
nutschCommented:
If you have parameters, then you can either push everything from Access, or ask for them on the excel side.
0
thutchinsonAuthor Commented:
Thanks, nutsche.  I've been playing in Microsoft Query and I'm having good success. This is real cool stuff.  I hadn't worked with Microsoft Query before.

One problem:
I'm pointing to a front-end .accdb with many complicated queries. While testing this,  I was unable to add many of these queries (as tables) to the Microsoft Query builder.  I was getting an error message "Unable to connect to the database [my database]".  To get around this, I had to rebuild many of the queries in Microsoft query from tables or simpler queries in the database. Do you have any idea what causes this or how to predict which ones will worK?  

0
nutschCommented:
Are these select queries? I don't have Access so I can't really test.

T
0
thutchinsonAuthor Commented:
Yes.  Just select queries.  I seems a shame to have to re-write so many queries.
0
nutschCommented:
I've asked some outside help on that.
0
aikimarkCommented:
@thutchinson

The Microsoft Query engine inside Excel is very limited.  If you are doing anything other than inner joins with just a few tables, you will encounter errors.

If your pivot tables are getting their data directly from Access, there ought to be a way of refreshing the data, similar to a .Requery method.  Can you post a sample workbook and Access database?
0
thutchinsonAuthor Commented:
@aikmark:
Thanks for stepping in. I really appreciate your comment about the limited functionality of Microsoft Query.  I hate to spend a bunch of time trying to figure out something before learning that the source of the problem is an application limitation.
It seems that the heavy lifting must be done within Access first. I'm working on cleaning up the queries in Access.  I may need to "Make Table" in Access and address that table from Excel.  I'll keep you posted.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.