How to create a new modified connection when I make a copy of a pivot table?

I have a pivot table which pulls data via a sql query. Let's call it 'Pivot Table 1' which has a Connection named 'Connection 1'. I want to make a copy of Pivot Table 1 (let's call that copy 'Pivot Table 2' ) and establish a new connection for it named 'Connection 2' and then modified the query in Connection 2 to pull slightly different data.

However, when I create Pivot Table 2 and change the name of the connection string to 'Connection 2' at the top of the Connections dialog box and modify the query this also changes the connection to Pivot Table 1. Excel thinks I want to use the same connection for both pivot tables and that I am simply Changing from 'Connection 1' to 'Connection 2'.

When this didn't work I then decided to create separate odc connection files for each pivot table by using a text editor on the Connection1.odc file and editing it to create a Connection2.odc file (I change the 'TITLE' and the Command Text). Now I'm trying to figure out how I can point each pivot table to its separate connection file.

Can anyone help?

Thanks