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

SydneyGeek

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

KurtVba

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

KurtVba

New Member
Joined
Oct 25, 2009
Messages
22
Hey Fazza,

How can I re-create the ALT DDN or DP functionality with VBA?

Kurt
 

Fazza

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

KurtVba

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

Fazza

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

SydneyGeek

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

Forum statistics

Threads
1,085,429
Messages
5,383,622
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top