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

xiaoqin

New Member
Joined
Sep 16, 2010
Messages
1
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

KurtVba

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

KurtVba

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

Watch MrExcel Video

Forum statistics

Threads
1,089,790
Messages
5,410,432
Members
403,318
Latest member
mistro333

This Week's Hot Topics

Top