Very Tricky - Change SQL with VBA

sh1pley

Board Regular
Joined
Dec 22, 2006
Messages
160
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
Dec 22, 2006
Messages
160
That showed me where the error is. Its not replacing "live" with "dream" in the SQL code
 

Watch MrExcel Video

Forum statistics

Threads
1,123,085
Messages
5,599,650
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top