Redirect source of pivot table

[Guest]

Client inherited some files with pivot tables that are pulling data from an
Access database. The access database was moved to a different drive so when
she tries to refresh the database, she gets an error that the file can not be
found. We have tried to redirect it to the correct location by browsing and
selecting the file but we then get a dialog box asking for the administrator
password. If we leave the pasword field blank, we then get a message that
query can not be edited with MS Query. She is able to open the database and
is not prompted for any passwords. How can we redirect the pivot table to the
new location of the database?

 

[Guest]

See if the Pivot Play addin at Debra Dalgleish's website handles your
situation:
http://www.contextures.com/xlPivotPlay01.html

Post back with any questions.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

We have downloaded and installed the add-in.
The information it displays for the current locaction of the data is:
ODBC;
DSN=MS Access Database;
DBQ=H:\Dave\MAINOfficeDepot\WeeklyPOS\WeeklyPOS.mdb;
DefaultDir=H:\Dave\MAINOfficeDepot\WeeklyPOS;
DriverId=25;
FIL=MS Access;
MaxBufferSize=2048;
PageTimeout=5;

SELECT qry_weeklyPOSpull.`SKU Number`, qry_weeklyPOSpull.`Vendor Stk Nbr`,
qry_weeklyPOSpull.Week, qry_weeklyPOSpull.`Week#`, qry_weeklyPOSpull.Year,
qry_weeklyPOSpull.DeptDesc, qry_weeklyPOSpull.Category,
qry_weeklyPOSpull.SubCategory, qry_weeklyPOSpull.Color,
qry_weeklyPOSpull.Size, qry_weeklyPOSpull.`SumOfRetail Sales`,
qry_weeklyPOSpull.`SumOfCost Sales`, qry_weeklyPOSpull.`SumOfUnit Sales`,
qry_weeklyPOSpull.`SumOfQuantity On Hand`, qry_weeklyPOSpull.`EQv Units`,
qry_weeklyPOSpull.`Location Type`
FROM `H:\Dave\MAINOfficeDepot\WeeklyPOS\WeeklyPOS`.qry_weeklyPOSpull
qry_weeklyPOSpull


The path of the database now is L:\MainOfficeDepot\WeeklyPOS\WeeklyPOS.mdb
We have changed the path in the 3 places we see the path listed (DBQ,
DefaultDir and FROM `H:\Dave\MAINOfficeDepot\WeeklyPOS\WeeklyPOS`.)- but we
then get a syntax error. Unfortunately it doesn't tell us where the syntax
error is. We even tried typing in the entire name of the server, folder, etc.
but got same error. Are we supposed to manually type in the new path? Or is
there a different way to redirect it.
Thanks for any help you can give us with this. They really don't want to
have to recreate all those pivot tables!

 

[Guest]

Try this:

In a new workbook on the SAME COMPUTER that will be running the pivot table.
Build a simple pivot table sourced to the MS Access file and making sure you
use the UNC (eg \\servername\folder\myfile.mdb) and not a mapped drive.

Then check the settings of that pivot table with Pivot Play.
Copy the Connection String into a notepad file.

Return to the production pivot table.

Engage Pivot Play and paste the notepad version of the connection string
into the edit window. (Also copy the MDB reference into the SQL string where
applicable.)

Save the settings (which will update the data, if successful....otherwise,
the prior settings will be restored)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

We were able to solve the problem. It turned out that the Access database was
linked to another database. We needed to refresh those links and then it
worked.
Thanks for your help!

 

[Guest]

the Access database was linked to another database. We needed to refresh
those links and then it worked.<<

THAT I would not have guessed anytime soon. : )

Thanks so much for the update.


***********
Regards,
Ron

XL2002, WinXP