New SQL Express so need to update Connection

[finspa]

Hi All

I have a report set up with around 100 pivot tables set up connected to a SQL Express Connection. I have been given a new PC at work so I have a new instance of SQL Ex.

I have 'restored' my old tables onto my new machine and now I need to point my pivot tables at the new version.

My initial reaction was to change the pc name in the properties - Definition - connection string, but this doesn't seem to do anything!

For example changing the bold bits here...

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Markets;Data Source=OLD-PC\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=OLD-PC;Use Encryption for Data=False;Tag with column collation when possible=False

to

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Markets;Data Source=NEW-PC\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=NEW-PC;Use Encryption for Data=False;Tag with column collation when possible=False

Is there a way to update the connection without having to recreate all the pivots and charts?!

Many thanks

 

[mole999]

if those strings are in the VBA, just a text find and replace ?

 

[finspa]

if those strings are in the VBA, just a text find and replace ?

Many thanks for your reply, however, I am not using VBA, this is just the connection properties.

 

[Norie]

Can't you right click the pivot tables, select Change Data Source... and point them towards the new instance of SQL Server?

 

[finspa]

Can't you right click the pivot tables, select Change Data Source... and point them towards the new instance of SQL Server?

I have just created a new connection and was attempting to do exactly as you suggested. The problem is, I have a lot of pivot tables and quite a few slicers so, apparently, I have to remove the slicer connection from each pivot table, change the data source for each and then reapply the slicer.

I was really hoping I could just amend the existing data source/connection!!

 

[Norie]

When you changed the connection as indicated in your first post you said nothing happened.

What do you mean exactly?

Did you get any errors when you refreshed the pivot tables/slicers etc.?

When you refreshed were they not actually updated?

 

[finspa]

When you changed the connection as indicated in your first post you said nothing happened.

What do you mean exactly?

Did you get any errors when you refreshed the pivot tables/slicers etc.?

When you refreshed were they not actually updated?

Hi Norrie

There was no error message, it seemed to work, however, it did not load the data.

The report I am running is based on Sales per quarter. The SQL table I connect to now has Q1 2018 in, so obviously I want to include that.
When added the new data connection, the Q1 2018 data is there, no problem. But, when I changed the connection string as above, the report refreshed and my "Quarter" slicer showed "Q1 2018" but it was greyed out, as in there was no data there.
The original data seemed to refresh OK but I just assumed that was something to do with the cache.