Changing a pivot cache Connection in Excel 2003

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;
paulmcneilAsked:
Who is Participating?
 
puppydogbuddyCommented:
see this link from Microsoft (code included):
          http://support.microsoft.com/kb/816562
0
 
paulmcneilAuthor Commented:
Thanks very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.