Very Tricky - Change SQL with VBA

[sh1pley]

I have hundreds of external data sources that need changing to a new server

To do this manually I have to:

Edit Query - Change Server
Change the database from Live to Dream
OK

Then I need to change the SQL to replace
live with dream
plus a few others as seen in the code below

Then I return the data to excel

This is the code I have tried to come up with. It seems to pass through all the lines but when it gets to the refresh statement it brings up the box in excel that asks for the server and database name again???

Sub ChangeSQL()

Dim OldConn As String
Dim NewConn As String

OldConn = "ODBC;DRIVER=SQL Server;SERVER=BLISS001;UID=report user;PWD=reportuser;APP=Microsoft Office XP;WSID=BLISW016;DATABASE=live;Network=DBNMPNTW;"
NewConn = "ODBC;DRIVER=SQL Server;SERVER=BLISSSQL01;UID=report user;;APP=Microsoft Office XP;WSID=BLISW016;DATABASE=DREAM;"

Sheet5.QueryTables.Item("Query2").Connection = Replace(Sheet5.QueryTables.Item("Query2").Connection, OldConn, NewConn)
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('live')", "WHERE ('DREAM')")
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('M_APBUDGET')", "WHERE ('M_BALANCE')")
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('BUDGETCODE')", "WHERE ('TRANTYPE')")
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('M_BALANCE2')", "WHERE ('M_BALANCE')")

Sheet5.QueryTables.Item("Query2").Refresh

End Sub

 

[Richard Schollar]

Hi Sh1pley

Have you tried inserting a Debug.Print statement after the

Sheet5.QueryTables.Item("Query2").Connection = Replace(Sheet5.QueryTables.Item("Query2").Connection, OldConn, NewConn)

code to see if the change has really taken effect?

 

[sh1pley]

That showed me where the error is. Its not replacing "live" with "dream" in the SQL code