I have an Excel 2003 workbook with 6 sheets and a pivot cache for each sheet. Each pivot cache SourecTYpe=1, i.e., the SourceData is an Excel range in the workbook. Using Excel VBA, I want to change the source of the data for all six pivot tables to an MS Access 2003 table. I believe I first need to create a new pivot cache that has SourceType=2 and strings for Connection and CommandText. I need sample code for:
-creating a new pivot cache that has its source data in MS Access
-change the source data for the existing pivot tables to the new MS Access pivot cache
Also, do I delete the old Excel caches?