Pivot query cannot be referenced in Excel

[syswizard]

I created a slick transform query in Access whereby the number of columns could grow as users can enter new pivot values.
Excel will not permit this type of query to be accessed in a connection / queryTable.
So now I am stuck with creating a temp table from the query resultset.
However, temp tables only reside on the front-end...so after I create it, I somehow must export it to the backend and delete the former version.
Then I think I have to refresh the link to that table.

I just wonder in lieu of all of this trouble, that I should just create a fixed set of Pivot names and make them generic like "A1","A2","A3","A4", etc.
In that manner, the underlying table will have enough of reserved columns for future additions of new values.

What say yee ?
With the latter approach I would have to create some dynamic SQL to make the pivot values into their corresponding fixed table columns.

[HansV]

Wouldn't it be easier to create a pivot table in Excel based on an ordinary select query in Access?

[syswizard]

Wouldn't it be easier to create a pivot table in Excel based on an ordinary select query in Access?

Hans - you bring up an interesting point.....what if I were to insert the SQL string of the Transform/Pivot into the commandtext of the connection ?
Would it execute the Transform/Pivot properly ?
Thanks for this...I'll try it tomorrow.

Otherwise, I've decided to create a perm back-end table with up to 10 columns...each with generic names...A1,A2,A3, etc.
Then I just change the INSERT statement in the query to reflect the number of pivot values.
INSERT INTO PermTable(A1, A2, A3)
SELECT * FROM qryPivot
;

[syswizard]

Wouldn't it be easier to create a pivot table in Excel based on an ordinary select query in Access?

I was trying to avoid yet another pivot table....so many present already.

Hans - it worked !! I was able to paste in the SQL in commandtext and the transform/pivot query worked....lightening fast.
So that's another bug related to connections...the selection list for the data source ignores pivot queries....

If the SQL string worked I figured I would manually enter the name of the query as a Table type of data source.
Sure enough, that worked fine as well.

BTW: I still have not been able to determine why connections created with Excel 2013 cause Excel 2010 to crash. I've done everything to trim the connection string down to it's most fundamental components.
Apparently there's some sort of "unexposed" property that I cannot change.

[HansV]

It's not a bug, it's by design, since the columns returned by a crosstab query are dynamic.

When you specify the SQL explicitly, the engine has to determine the columns on the fly anyway.