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

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

KurtVba

New Member
Joined
Oct 25, 2009
Messages
22
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
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,264
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
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

KurtVba

New Member
Joined
Oct 25, 2009
Messages
22
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
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,264
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
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,264
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
 

KurtVba

New Member
Joined
Oct 25, 2009
Messages
22
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
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,264
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)
 

KurtVba

New Member
Joined
Oct 25, 2009
Messages
22
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,089,756
Messages
5,410,254
Members
403,305
Latest member
tray2014

This Week's Hot Topics

Top