> 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
>
>
> "maryj" wrote:
>
> > 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!
> >
> > --
> > maryj
> >
> >
> > "Ron Coderre" wrote:
> >
> > > 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
> > >
> > >
> > > "maryj" wrote:
> > >
> > > > 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?
> > > > --
> > > > maryj