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
 
Without MS Query, you can use VBA. For example, use ADO to populate a recordset. If you need to filter, one way might be simply to filter the recordset. Then set the Excel pivotcache recordset to the same recordset, and complete the pivot table. There was a thread on Friday that had a similar idea. I'll have a look for a link.
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi Fazza, Hi Denis,

You both make an excellent team!

After an hour of testing the MS Excel/Access db to re-create the problem and after talking to the ecstatic user, you are both right.

Fazza, as you wrote in Post#29, the problem cannot exist. You are right! First off, I confirm these are not MS Access parameter queries. The Excel Pivot Table Refresh button now works flawlessly. I cannot re-create the error messages from the operational queries.

Denis, I tried re-creating some expressions (e.g. "LABEL: Something + Something") in MS Access as per your Posting #30. The queries did not create any errors, BUT I later went through some older, now unused MS Access queries and found one that did create the error: "Too few parameters. Expected 1.".

Using Fazza's ALT DDN in MS Excel, a field "Expr1000" came up in the menue "Query Wizard-Choose Columns". This expression represents in the MS Access created Query a Field/column criteria match: " (LIKE... OR LIKE...etc) AND NOT LIKE... ". Why, this causes an error but the other expressions with labels do not, I do not know.

In any case, the MS Excel Refresh button now works. I have asked the ecstatic user to be sensitive to this issue (and to us!) in the future. After some preliminary work last week, she will start working with the db more and more extensively sometime within the next weeks.

If any thing comes up again, believe me, you guys will be the first to know!

Thanks!
Kurt
PS The ecstatic user is a wonderful German native. She knows the beer country pretty good :)
 
Upvote 0
Hi Xiaoqin,

I would appreciate the code if you can find it. This would maybe help me understand why the MS Excel ALT DDN works so quickly, assuming the VBA code works just as fast for the user who uses MS Excel's Pivot Table.

In other words, I am looking for VBA code that does not load up the main memory or up load the data records from MS Access into MS Excel. I am just trying to create the same environment as the MS Excel ALT DDN which uses simple MS Access Queries (not Parameter Queries) of tables located in MS Access.

Thanks for any support.

Kurt
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,728
Members
448,294
Latest member
jmjmjmjmjmjm

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