Excel 2007: Build Pivot Table with MS ACCESS Query (View)

KurtVba

New Member
Joined
Oct 25, 2009
Messages
22
Hi to that special MS Excel/Access Guru,

My problem probably can be solved with a simple, but important click somewhere. I have surfed about 6.5 hours to find this click to no avail.

MY GOAL
is to use Excel 2007 Pivot Tables to summarize the filtered data records found in a MS Access 2007 QUERY. Thus I can harness the power of Excel Pivot Table functions with the efficiency of Access's database organization (memory optimization plus indexing speed features). According to Mr. Excel in his book PIVOT TABLE DATA CRUNCHING book for Excel 2007, this should be a couple of clicks.

THE PROBLEM:
I tried building an Excel Pivot Table using an Access query (view). I can build the link and populate the fields in the Excel Pivot Table, but I get zero (0) records (rows of data) in the Pivot Table. The query I am trying to connect to in Access works perfectly in Access.

TAB STEPS IN EXCEL
which I followed are found in Mr. Excel's book on page 180: Data, From Access, Selected database, Select table or view (query).

THE UNDESIRED RESULT
I have been able to get a TABLE (but again, not the QUERY records) out of MS Access into the Excel Pivot Table.

ALTERNATIVE APPROACH (tried without success)
I also tried the other way suggested in Mr. Excel's book on EXCEL 2007. I used the tabs: Insert, Pivot Table, Use external data source, Connections, Browse for More, Select data source, Select Table or View (i.e. Query). I also was able to get the TABLE records (data rows) in Access, but not those of the QUERY, into Excel's Pivot Table.

Both the Excel and Access files were in the Trust Center, as Mr. Excel recommended. I wish to avoid using the old MS Query function alternative at this time--simple things for simple minds.

There is probably a simple click solution somewhere. Who knows how to get the Access (2007) QUERY records into an 2007 Excel Pivot Table?

Thank you for you time and advice in advance!
Kurt
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi Fazza,

I wanted to create the Excel PT from an Access (Query with a LIKE in it)--from your point of view, the latter, easy way.

I have already tried using the following steps: Excel Tab: Data, From Access, Selected database, Select table or view (query). I chose the Access Query (with a LIKE criteria in it) and get zero records / rows.

If the Access Query has NO criteria in it (i.e. just choosing fields), I can use it in the Excel PT, but I need filtered records / rows.

How can I create the Excel PT from an Access (Query with a LIKE criteria in it) ?

Kurt
 
Upvote 0
Thanks, Kurt. Sorry, I can't help much with this: I don't use 2007. All I know is like Denis originally wrote, "It should just work; ...". Without using MS Query, and whether or not it works you'll only know after trying, an option is to connect via ADO and make a recordset, then the PT. You might have some examples already? Just be sure to use the correct connection for 2007. Such as can be found if you google for "connection strings" OK? If you want I can google for some examples. regards, F
 
Upvote 0
As Fazza said you can use ADO to build the query in Excel.
Check out this tutorial, especially Part 5, for an example of how to create a query with parameters/criteria.
I'll see if I can dig out something I did a while back, creating the pivotcache from an ADO recordset.

Denis
 
Upvote 0
Hi Fazza, Hi Denis,

Fazza,
I looked at your links with the VBA code. That is impression code! I did not know you could use just SQL statements within Excel--so cool! I learned really something game-changing for the future.

Just the same, this code does not help me now with the goal at hand which is to use the back-end power of Access (memory optimization + indexing speed) with front-end presentation ease of Excel.

Denis,
Fazzi & I both understood you to say "This should just work". Did you ever test / verify in Excel that you could not directly connect to filtered data through an Access Query WITH criteria (see Posts 3 + 4 of this thread) following the steps recommended by MrExcel (Excel Tabs: Data, From Access, Selected database, Select Query).?

Thank you both for your advice and patience!
Kurt
 
Upvote 0
Kurt,

Maybe some slight misunderstanding, my comment about what Denis wrote is not simply I understand the comment but think the same thing. Many times I have created an Excel pivot table from an Access query, including queries with criteria. It is routine & 'just works'. And the steps for doing this manually are either from ALT-D-D-N or ALT-D-P, and then follow the wizard.

regards, F
 
Upvote 0
PS, Regarding creating the pivotcache from the ADO recordset, the thread I linked to above refers. 'the line "Set objPivotCache.Recordset = objRS" sets the pivotcache's recordset to that object'. I'm expecting you'll have success with the code solution. F
 
Upvote 0
I have looked at both the Fazza's code and the Denise's tutorial. I understand both. Thanks for these high-quality links.

In regards to memory utilization, does putting data in the PivotCache take up much more memory than if the Excel VBA code just somehow connects directly to the Access Query?

The reason I ask is because I have large data sets (<100K records), and the Excel PT takes minutes to rebuild during analysis. My understanding out of MrExcel's books is I can speed up this process by leaving the data in Access and analyzing it with Excel PT. This is the code I think I am looking for.

What do you both think?

I am going to install in the next days MS Access on another computer with the intent to test the Excel/Access External data link to a Query with Criteria. (Tabs steps: Data, From Access, Selected database, Select Query?

I'm ready for 'ein Weizen bitte' (please)!
Kurt
 
Upvote 0
AFAIK, putting the data in the pivotcache can take up more memory. The data has to be there.

If Excel just connects to the data in Access you don't need the data in Excel. You can set (in Excel 2003 and earlier, anyway, I can only imagine it is the same in newer versions) via pivot table options to not save the underlying data with the table. If this option is chosen, the Excel file can be tiny. A refresh or such might take ~1 or ~2 seconds if there are ~100,000 records.

I just tried with some data here, 152 columns and ~55,000 rows. The pivot table set up by connecting to data in MS Access (using the Excel external data route). Excel file size was ~52Mb. I then changed the PT setting to not save the underlying data and the file size dropped to <100kb.

(y) http://www.weihenstephaner.de/ (y)
 
Upvote 0
Hi Fazza,

I tried both ALT DDN & ALT DP, and they worked perfectly! I installed Access on another computer and it worked perfectly!

Initial tests also show the Excel PT responses much faster too!

I tried the connection over Excel's External Data, Access, Queries again-->no records. I cannot figure that out.

In any case, I'm ready really for mein Weizen bitte!

Thank you for ending my day on a high note!
Kurt

PS My previous post with questions are still open. I'd still be interested in hearing what you or Denise have to say about the memory & code. Will curiosity kill this cat?
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top