How to change the DSN that a pivot table is using?

[Dan Waterloo]

I have a pivot table hooked into an MS Access database using a DSN. I'd like to change the DSN I am using. The new DSN will point to another database that contains a query with the same name that I am basing the pivot table on.

I can't see how to do this. Help please?

 

[Dan Waterloo]

Okay, I can figure out what connection string a pivot table is using by checking out things such as

?ActiveWorkbook.PivotCaches(1).Connection

in the immediate window of the VBE and I end up seeing strings such as

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

If I want to repoint the pivot table I can edit the pivot cache connection using VBA. However, this string makes no reference to the DSN I used when creating the pivot table. Renaming the DSN or deleting it has no impact on how this pivot table now operates. It was just an easier way for me to create this connection string in the pivot cache in the first place.

The same thing is true for Excel queries built using DSNs.

?Activesheet.QueryTables(1).connection

will give me the connection string the query is now using whether I used a DSN to build the connection in the first place or pointed the query directly at the database.

At least this is now my understanding. If anyone has a better way of explaining this feel free to go ahead.