Changing a pivot cache Connection in Excel 2003

Posted on 2010-01-04
Last Modified: 2013-11-27
When I originally create a pivot table by hand in Excel 2003 using the Get Data button, I have to use the <New Data Source> option in the Database tab of the Choose Data Source window. So I create a New Data Source and give it a name, e.g., FY2009Data. Eventually I finish the pivot table and everything is fine. Then I want to use Access 2003 VBA to chaneg the PivotCache Connection of the workbook's pivot cache. I've tried this by getting the current pivot cache connection, e.g.,

ODBC;DBQ=C:\AnnualReports\LPG_Sales.mdb;DefaultDir=C:\AnnualReports;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;

If I re-set the pivot cache connection using the following VBA code which simply uses a differnt mdb name, I get an error when I try to refresh the pivot table. To repeat, the code for re-setting the pivot cache connection string does not raise an error but the refresh action in Excel 2003 does. The errors are:

[Microsoft][ODBC Microsoft Access Driver] Not a valid Password.
Error obtaining data

ODBC;DBQ=C:\AnnualReports\NEWLPG_Sales.mdb;DefaultDir=C:\AnnualReports;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
Question by:paulmcneil
    LVL 38

    Accepted Solution

    see this link from Microsoft (code included):

    Author Closing Comment

    Thanks very much!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now