Creating a Pivot Table from a valid SQL statement without MS Query
 

I have a database query that I'm trying to turn into the basis for a pivot
table. I know that the syntax is valid because I can run it throuh Crystal
Reports SQL Designer and it retrieves information just fine. The same syntax
fails when I try to retrieve it through MS Query. It fails on something as
basic as renaming a column from the database column name. I've managed to
programmatically create a database query by setting the SQL statement for the
CommandText property for the QueryTable object directly and it returns as
many rows as it can fit and display on the spreadsheet. I discovered that the
capacity of a PivotCache exceeds the 2^16 row limitation in Excel by running
a valid query that returns a significant number of rows. I'd like to use that
fact to my advantage, but I can't manage to set the CommandText for the
PivotCache object in the same way as I can for the QueryTable object
regardless of how basic the query is. If anyone has a suggestions for getting
this to work, it would be greatly appreciated.