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.
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.
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
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?
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...
For Each prm In qdf.Parameters