Excel 2010 Re-assign MsQuery Connecton to Database

[Salut39]

Hi

I have an external database which I use to create reports in Excel via MS Query.
Now location of the server has changed and I need to re-assign all my queries to a new location. Is there a painless way to do it in one go?

Thanks
Yuri

 

[SkipVought]

Hi,

How did you originally set your QueryTables up in your workbook?

I almost always used VBA after I set up my QTs to refresh and maintain as I often had changes that were more easily handled via code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Salut39]

I have manually set them up. Location is external and requires ODBC connection.
I added link in User DSN using driver MySQL driver 5.2. The new database set in the same way but IP is different.

 

[SkipVought]

you can do it with VBA or manually, like right-click in QT > Table > External data properties > Connection properties (icon on right of Connection Name) > Definition TAB modify your Connection String, COPY to a safe location and PASTE into each Connection string as necessary.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JVFriederick]

Salut39

If you have a few connections (<10) the answer by Skip is the way to go.

If you have tons, or the data source changes frequently, then some VB code can be handy. The following example uses the "connection description" to
store a search / replace string used to make changes to ALL queries in the XL file at one time.

This code was written to change connections and SQL statements for data sources that reside in the same folder as XL file (TargetDirectory = ThisWorkbook.Path)
Note : Pivot table connections need a little extra help during the search/replace action, so I purposely name them to begin with "PivotTable..." so they are skipped by this process.

Without knowing your before/after IP address (server location) and other specifics I cannot be more specific.

JVF

Sub MapCurrentConnections_Default()
'
' LOOK IN THE QUERY DESCRIPTION TO DETERMINE PREVIOUS PATH, WHICH WAS PLACED THERE FOR THIS PURPOSE
' REPLACE PORTION OF SQL STATEMENT, CONNECTION PARAMETERS, AND DESCRIPTION WITH NEW PATH (BASED ON LOCATION OF THIS FILE)
' THIS PROVIDES THE MECHANICS FOR EACH QUERY TO CHANGE PARAMETERS BASED ON LOCATION OF XL FILE

Dim sQuery As WorkbookConnection
TargetDirectory = ThisWorkbook.Path & "\" ' TARGET FULL PATH

For Each sQuery In ActiveWorkbook.Connections
    If InStr(sQuery.Name, "PivotTable") = False Then ' ONLY PROCESS NON-PIVOT TABLE CONNECTIONS
        DoEvents: Application.StatusBar = "Query : " & sQuery.Description & " - " & sQuery.Name
        rString = sQuery.Description ' CONNECTION DESCRIPTION - USED AS REPOSITORY FOR SEARCH/REPLACE STRING
        sQuery.Description = Replace(sQuery.Description, rString, TargetDirectory) ' CONNECTION DESCRIPTION FOR NEXT TIME
        sQuery.ODBCConnection.Connection = Replace(sQuery.ODBCConnection.Connection, rString, TargetDirectory) ' CONNECTION PARAMETERS
        sQuery.ODBCConnection.CommandText = Replace(sQuery.ODBCConnection.CommandText, rString, TargetDirectory) ' SQL STATEMENT
    End If
Next sQuery

Application.StatusBar = Empty: Set sQuery = Nothing

End Sub

 

[Salut39]

Hi guys thank you for your replies but I still have a problem reconnecting.
doing it Skip's way I am getting an error: "Select command denies user..."
as for VBA not enough knowledge to modify.

Old Connection: DSN=Cope dBase;UID=cope;DESCRIPTION=UMGI Cope dBase;SERVER=10.30.2.48;DATABASE=cope;PORT=3306;AUTO_RECONNECT=1;
New Connection: DSN= Sales.Log;UID=cope;DESCRIPTION=Production dBase;SERVER=10.44.102.31;DATABASE= dstores;PORT=3306;AUTO_RECONNECT=1;

I want to keep all parameters & SQL statements the only change required is location

Yuri

 

[JVFriederick]

Salut39

There is a space after the = sign in your new connection string, would check that first.

.....DATABASE= dstores;PORT=3306;.........

Would then suggest manually creating new connection on another sheet and comparing the strings.

Since you need changes to DSN, IP address, and DATABASE name there are multiple possibilities.

Lastly, are you checking the actual SQL statement? Depending on driver it may need attention as well.

JVF

 

[Salut39]

I managed to make it work by making adjustment in SQL statement. But this is not the fastest way to update I had in mind I have lots of queries.

Thanks

 

[JVFriederick]

Salut39

If you want something faster, please post before and after SQL statements, and before and after connection strings.

Your original request stated the server location changed, which I incorrectly assumed meant just the IP address.

Obviously more than just one thing changed, which makes a solution more complex. The advice Skip gave was spot on, manual or VB code.

JVF

 

[Salut39]

I think the issue is in the name of database, so after the change of the name the SQL "where..." requires changing database name. I see if IT can rename it to the old name. this may speed up the change. I posted old and new location. I am not sure where insert it in your macro?