New SQL Express so need to update Connection

finspa

New Member
Joined
Jan 15, 2015
Messages
42
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,518
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
if those strings are in the VBA, just a text find and replace ?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Can't you right click the pivot tables, select Change Data Source... and point them towards the new instance of SQL Server?
 

finspa

New Member
Joined
Jan 15, 2015
Messages
42

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 15, 2015
Messages
42
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top