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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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?

Looks like I just had another sex change! ;)

I haven't tracked the memory usage before, but pulling data straight into a pivot table certainly runs quickly. In the case where I used it, refreshing the pivot table took 30 seconds -- and about 29 of that was processing and cleaning the data before I pulled it down.

Denis
 
Upvote 0
Denis,

Oh, I can feel for you! This shot-in-the-arm of 'speed' from Fazza must of rocked you, especially since you are a consultant expert for both Excel & Access.

It made my day too! I'm just jumping-up-and-down happy with Fazza :biggrin:
I'm just now preparing the new database. Can't wait till I am finished.

Thank you Denis too for supporting this dialog & not giving up!
 
Upvote 0
Hi, Kurt.

I already posted some links to VBA approaches - using ADO recordsets. I didn't look but maybe Denis's code covers this too? If you want more like the wizard, please use the macro recorder from Excel. So, ALT-T-M-R and then continue. Or do you just want the wizard dialog box to appear? I'm not 100% clear - is the original question answered & this is a new one?

BTW, note, I don't use Excel 2007 so can't help with details to suit that version.

regards

PS. For an Excel VBA solution, if you don't get a good response here, I suggest you might be better to start a new thread in the Excel section. The Excel 2007 programmers will see it then. F
 
Last edited:
Upvote 0
Hi Denis, Hi Fazza,

Just a quick update: The tool went live late last week.

The Excel table with 30+K records was regularly crashing when the Excel Pivot Table function originally ran. With the data now in MS Access tables/ queries and linked (somehow) to Excel with Alt D D N, the user is experiencing extremely fast responses with her Excel Pivot Table (PT) function. She is ecstatic (about the PT speed, not Denise's sex change).

The only trouble she has now is she is finding many errors in the multi-user database, which then she corrects directly in the MS Access tables / queries. Afterward, she cannot just refresh the Pivot Tables in Excel to update for the corrections. She has to rebuild each and every one of the PTs, using the Alt D D N method. The error message she gets when she tries to refresh is: "Microsoft ODBC. MS Access Driver: Too few parameters. Expected 3. Problems obtaining data."

Is there a faster way than just rebuilding all the Excel PTs?

Kurt
 
Upvote 0
Faster would be to have VBA do the work.

But the root cause of this isn't clear to me, and needs attention.

With data in a simple Access query, all this should be straightforward and the problems that have arisen don't exist. So, there is something unknown still.

Are the datasources queries with parameters?

PS
I see, Kurt, that post #3 of the thread says they're not parameter queries, but I would still like to 'double check'.
F
 
Last edited:
Upvote 0
Kurt, I did a search on the error and came up with this in the MS Support system:
This error occurs only with Microsoft Access when one of the column names specified in a select statement does not exist in the table being queried.
Are there any fields that are expressions in the original Access query?

eg -- something like ExtendedPrice:ItemPrice*Quantity

You may need to refer to the field using the expression (ItemPrice*Quantity) instead of the alias (ExtendedPrice), in any sorting or criteria clauses.

Alternatively (I see this in DAO recordsets, possibly it translates to ADO too), you may need to evaluate all criteria before opening the recordset. I first define a QueryDef, then evaluate the parameters, then open the recordset, like this...

Code:
Set qdf=CurrentDb.CreateQueryDef("",sSQL)
For Each prm In qdf.Parameters
  prm.Value=Eval(prm.Name)
Next prm
Set rst=qdf.Openrecordset
'...continue here
I'll see if I can dig out an ADO equivalent.

UPDATE -- check out this link

Denis
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
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