Learn how to a build a cloud-first strategyRegister Now


Changing a pivot cache Connection in Excel 2003

Posted on 2010-01-04
Medium Priority
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

puppydogbuddy earned 2000 total points
ID: 26175133
see this link from Microsoft (code included):

Author Closing Comment

ID: 31672608
Thanks very much!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

864 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