changing data source of a pivot table

[Marckas]

I have a number of pivot table sheets feeding of a query. I have created a new field in the query and created a new pivot table worksheet. Now I want to move the old existing sheets to the new one. When I move them to the new workbook, I noticed it still feeding of from the old query. Is there any way I could change the data source or do I have to recreate all the existing pivot table sheets.

 

[AnotherHiggins]

Go to the Pivot Table Wizard, then click on the back button.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

So you already tried hitting the BACK key in the PT Wizard and that did not work, and your PT is in Excel and that's why your posting here as suggested?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Marckas]

well, the probelm is I created the pivot table from the MS Access Pivot table wizard with a heavy query that is made up of licked SQL Server tables. When I try going thru hitting the back button in excel located in the pivot table wizard, I get an error after I select the external data source which is the query in access. I get ODBC connection to sql server failed. I was just simply trying to add an additional field to an existing pivot table. I included the new field to the query that is feeding the existing pivot table but the pivot table did not pick it up. So I went thru the pivot table wizard in access as a new form and selected the query with the new field and it worked. The problem I have now is that all the existing 24 pivot table sheets is still feeding of the old query that does not containg the new field that I added. So instead of me recreating all the 24 sheets, I simply just want to change the data source to pick up the updated query with the new field. The new field is from sql server which I have set up as a linked access table.

 

[SkipVought]

Your query in Access is to a linked table.

If you use essentially the same query, it is NOT thru Access. It's directly to the SQL Server database. BTW, the SQL syntax will be somewhat DIFFERENT when you're not in Access.

Do you have a DRIVER for your SQL Server database configured?

Start > Control Panel > Administrative Settings > Data Sources (ODBC)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Marckas]

yes I do. BTW thank you for your response. I have a DNS setup. I am getting the field from a DQL server table. I created a linked table in access to the table in SQL Server. I then created a quesry in Access that feeds of the pivot table. I know it would be so much easier to just do it directly from SQL Server. But the Access application is kind of like a Portfolio Analyzer that a lot of other users use. So when you opne this Access application, a log in dialogue box pops up to log in SQL server. When i created the DNS, it then connects directly to sql server. I then created a DNS for Access and then when I try to change the data source it gives me the odbc connection to sql server error.