Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Redirect source of pivot table
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Redirect source of pivot table
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Redirect source of pivot table
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.md b; 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Redirect source of pivot table
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.md b; 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Redirect source of pivot table
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! -- maryj "Ron Coderre" wrote: 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.md b; 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Redirect source of pivot table
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 "maryj" wrote: 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! -- maryj "Ron Coderre" wrote: 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.md b; 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Q: Change pivot table source path? | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Pivot Table Report formatting - can't select Data Source Order | Excel Discussion (Misc queries) | |||
pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Auto Update Data Source? | Excel Worksheet Functions |