1

I have an Excel 2013 file which shows data from an external DB2 database. The DB2 database has moved and I would like to change connection information to point to the new IP of the database.

How do I do this in Excel?

At the moment the only way I can find to do this is by creating a new connection and adding all of the views against that it is calling instead of just editing an IP address.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3165854
  • 1,505
  • 8
  • 48
  • 100

1 Answers1

2

should be simple enough, you just need to update the connection string

Go to...

Data --> Connections

There you should see a list of the connections used in the spreadsheet.
Select the connection(s), then click --> Properties.

On the Connection Properties dialog, you should see two tabs, select --> Definition.

You should find the information in the text area for --> Connection String

Change it and save it...

========================================================

To find connections made through ODBC connections...

Control Panel --> Administrative Tools --> ODBC

Look thru the tabs for User DSN -- System DSN -- File DSN...

Find the Data Source name found in the Connection String.

Select and click --> Configure.

WhiteHat
  • 59,912
  • 7
  • 51
  • 133
  • Thanks. I have looked at the connection string and I couldn't see the address so its got me confused. I'm testing it locally and the string it produced was `Provider=IBMDADB2.DB2COPY1;Persist Security Info=True;User ID=pdb;Data Source=PDB;Mode=ReadWrite` Where would I add localhost and I'm not sure that the Provider information is correct? – user3165854 Oct 07 '15 at 16:57
  • Thanks for the help. I think I found what I needed. It looks like I needed to use extended properties. In case its of any use to anyone here it is `Provider=IBMDADB2;Persist Security Info=True;Mode=ReadWrite;Extended Properties="Database=database_name;Hostname=IP Address;Protocol=TCPIP;Port=Port_number;Uid=Database;"` – user3165854 Oct 08 '15 at 14:28