I have to change the SQL on hundreds of queries. This involves changing the server, doing a find and replace on the command text which I have managed to do with the below code.
However I also need to add a line onto the command text.... but if this line already says TRANTYPE='SEP06' then I need to leave it as it is.
New line to add:
AND (D_DETAILS.TRANTYPE='DEFAULT'))
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("Query3").Connection = Replace(Sheet5.QueryTables.Item("Query3").Connection, OldConn, NewConn)
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "live", "DREAM")
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "M_APBUDGET", "M_BALANCE")
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "BUDGETCODE", "TRANTYPE")
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "M_BALANCE2", "M_BALANCE")
Sheet5.QueryTables.Item("Query3").Refresh
End Sub
However I also need to add a line onto the command text.... but if this line already says TRANTYPE='SEP06' then I need to leave it as it is.
New line to add:
AND (D_DETAILS.TRANTYPE='DEFAULT'))
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("Query3").Connection = Replace(Sheet5.QueryTables.Item("Query3").Connection, OldConn, NewConn)
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "live", "DREAM")
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "M_APBUDGET", "M_BALANCE")
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "BUDGETCODE", "TRANTYPE")
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "M_BALANCE2", "M_BALANCE")
Sheet5.QueryTables.Item("Query3").Refresh
End Sub