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) ?
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
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.
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.
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).?
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.
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
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?
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.