vba - Server moved, now MS Excel 2003 queries won't work -

We use MS queries to get data from various databases to create metrics (i.e. in Excel, on data Go ... Import External Data ... New Database Query) SQL queries and parameters (mostly Canon String) are automatically stored in Excel within the spreadsheet.

However, we have recently taken one of our databases to a new server. Consequently, Excel signals us for a new ODBC connection when trying to refresh the data, but it will not accept new values. We can fix new questions, so the ODBC connection is properly established, but we can not change any questions.

Is there any way to change the IP address for programming or these settings? I tried to change the XLS file in a hex editor (the IP address is visible there), but then it says that the workbook is corrupt.

If you need to change the connection string of your questions, then this macro will be able to query all questions in the active workbook. Will change this (you do not need to store the code in the workbook).

I include two sample connection strings - provides a DSN, provides a server / database if your DSN is OK while creating new queries, then try DSN first. If you use that, adjust the SQL Server version.

In addition, one has standard security (UID, PWD) and uses other Windows-based security (trusted connection). Mix and match as appropriate.

_{sub conversion command () Dim Quote as WorkTable compression strNEW_CONN_DSN string = "ODBC; DSN = MyDSN; Description = MyDescription; UID = myid; PWD = mypwd, as QuatTable dim wks" Const strNEW_CONN_SQL string = "Driver = {SQL Server Native Client 10.0}; Server = myServerAddress; Database = myDB; Trusted_Connection = yes;" ActiveWorkbook for each works Works for each QT in wks.QueryTables qt.Connection = strNEW_CONN_DSN next qt next wks finished sub}