To run DBCC Traceon, you require sysadmin rights. You can also programmatically change the connection string using the below example:
Code:
'ChangeConnection
Sub ChangeConnection()
Dim sh As Worksheet
Dim qt As QueryTable
Dim sConnection As String
For Each sh In ActiveWorkbook.Sheets
For Each qt In sh.QueryTables
'Show current connectionstring
MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: " & vbCr & qt.Connection)
'Show current query
MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr & qt.CommandText)
'Change Connection
qt.Connection = "ODBC;DRIVER=SQL Server;SERVER=myserver;DATABASE=myDB;Trusted_Connection=Yes;APP=Excel_TopCustomers;"
'Change Qry text (the owner for instance)
qt.CommandText = Replace(qt.CommandText, "DB.dbo.", "DB.Me")
qt.SavePassword = False
'Show new connectionstring
MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr & qt.Connection)
'Show new query
MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr & qt.CommandText)
Next qt
Next sh
End Sub
Bookmarks