Pivot table external source drive changes

[Joe C]

I did a search here but didn't find this question.

I am using pivotable with access queery as the source.

When I set it I cuse that external source browse tool, but it seems to only recognize the source if the dive is the same.
When I go downstairs to try on a users computer there dirive letter(K is different than my (H and it does not work.

 

[Andrew Poulsom]

Try changing the source to a UNC, eg \\server\folder\file.mdb rather than a mapped drive.

 

[Joe C]

I change this after I sellect it?

 

[Andrew Poulsom]

Yes.

Select a cell inside the pivot table and press Alt+F11 to activate the Visual Basic Editor. Press Ctrl+g to go to the Immediate window, type:

?ActiveCell.PivotTable.PivotCache.Connection

and press Enter. Edit the returned connection string so that it uses a UNC path, and copy it to the clipboard. Edit what you typed above so that it looks like:

ActiveCell.PivotTable.PivotCache.Connection = "[paste new connection string here]"

and press Enter.

 

[Joe C]

Many thanks

Especially getting back to me so quick.

I will give that a try. I was trying to change it in the msqueery, and the change was working(I have the right path) in MSqueery, but each time I went back to pivot table it had the other path.

 

[Joe C]

Did the first 2 steps

?activecell.PivotTable.PivotCache.Connection

ODBC;DSN=MS Access Database;DBQ=T:\ABY\db1.mdb;DefaultDir=T:\ABY;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

then under I pasted below hit return but get Compile error: expect expresion

activecell.PivotTable.PivotCache.Connection = \\safps03\SalesFinance$\aby\db1.mdb

 

[Andrew Poulsom]

You need to edit this:

Database;DBQ=T:\ABY\db1.mdb;DefaultDir=T:\ABY;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

so that it becomes:

Database;DBQ=//safps03/SalesFinance$/ABY/db1.mdb;DefaultDir=/safps03/SalesFinance$/ABY;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

Then:

ActiveCell.PivotTable.PivotCache.Connection = "Database;DBQ=//safps03/SalesFinance$/ABY/db1.mdb;DefaultDir=/safps03/SalesFinance$/ABY;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

Note the quotes around the connection string and the use of forward slashes instead backslashes.

 

[Joe C]

I am still having problems. I am going to practice changing technique to my C: drive. to make sure I am doing that part right before bothering you again.
Should I be able to Change the T to C as long as I have copy of folder on my C: Drive.

I am really sorry for bothering you so many times.

 

[Andrew Poulsom]

Yes, you should be able to use C instead of T.

 

[Joe C]

Hey I got it to work.
Thanks so much.

I am going to search or post another question but I figure your just going to have answered it or answer it.
Am I better size of file chosing where I house the pivottable data?

Again Thanks the stuff you know is amazing.