paulmcneil
asked on
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;Defaul tDir=C:\An nualReport s;Driver={ Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=M S Access;MaxBufferSize=2048; MaxScanRow s=8;PageTi meout=5;Sa feTransact ions=0;Thr eads=3;Use rCommitSyn c=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_Sal es.mdb;Def aultDir=C: \AnnualRep orts;Drive r={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=M S Access;MaxBufferSize=2048; MaxScanRow s=8;PageTi meout=5;Sa feTransact ions=0;Thr eads=3;Use rCommitSyn c=Yes;
ODBC;DBQ=C:\AnnualReports\
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\
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER