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?
 
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.