Change ODBC CommandText using Loop

[mountainredrockdog]

I have a spreadsheet which includes multipe queries that connect to the same "live" database via an ODBC connection. There are multiple sheets in the workbook; each sheet pulls a different query from the same "live" database using the same ODBC connection.

My goal is to allow a user to toggle between the "live" and "beta" databases via a macro. Specifically: I have successfully created VBA to change the ODBC connection string (i.e. old string with new string) for each query on each sheet and it functions properly. I would now like to use VBA to loop through all sheets and replace the word "live.dbo" with "beta.dbo" in the query command text for each query or queries included on each sheet. Is this possible?

 

[John_w]

Sub cct()

    Dim ws As Worksheet, qt As QueryTable
    
    For Each ws In ThisWorkbook.Worksheets
        For Each qt In ws.QueryTables
            qt.CommandText = Replace(qt.CommandText, "live.dbo", "beta.dbo")
        Next
    Next
    
End Sub

 

[mountainredrockdog]

Thank you, that answers my question exactly. An addendum: "live.dbo" is part of a greater string(s) throughout the command text - live.dbo.names, live.dbo.address, or live.dbo.employee, etc., in each CommandText. The above loop does not work, presumably because it never finds "live.dbo" by itself. Is there a way to make the change taking into account "live.dbo" is part of a greater string? My spreadsheet is macro enabled, as other vba-driven macros work properly.

 

[John_w]

The code should work if the exact text "live.dbo" is part of the CommandText, but not if the case of the letters is different, for example "Live.Dbo.address", because the Replace call is case-sensitive. Try this code instead, as the Replace is now case-insensitive.

Sub cct2()

    Dim qt As QueryTable
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        For Each qt In ws.QueryTables
            qt.CommandText = Replace(qt.CommandText, "live.dbo", "beta.dbo", compare:=vbTextCompare)
        Next
    Next
    
End Sub

 

[mountainredrockdog]

Your code and response both make sense, but still not working. When I run the above VBA, the Command Text remains unchanged. I think there must be some other piece of information that I am overlooking. If I figure it out, I will post my solution. Thank you.

 

[John_w]

I'd forgotten that there is an extra level of abstraction for database connections in Excel 2007+ - the ListObjects collection (see Query Tables Work In 2003 But Not 2007). Therefore try this:

Public Sub cct3()

    Dim ws As Worksheet, lo As ListObject, qt As QueryTable, prev As String
     
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            Set qt = lo.QueryTable
            prev = qt.CommandText
            qt.CommandText = Replace(qt.CommandText, "live.dbo", "beta.dbo", compare:=vbTextCompare)
            MsgBox "Previous CommandText: " & prev & vbCrLf & "New CommandText: " & qt.CommandText
        Next
    Next
    
End Sub

 

[mountainredrockdog]

That did it. This will greatly simplify what has been a complicated process. Thank you so much!

 

[MzCheez]

I'd forgotten that there is an extra level of abstraction for database connections in Excel 2007+ - the ListObjects collection (see Query Tables Work In 2003 But Not 2007). Therefore try this:

Public Sub cct3()

    Dim ws As Worksheet, lo As ListObject, qt As QueryTable, prev As String
    
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            Set qt = lo.QueryTable
            prev = qt.CommandText
            qt.CommandText = Replace(qt.CommandText, "live.dbo", "beta.dbo", compare:=vbTextCompare)
            MsgBox "Previous CommandText: " & prev & vbCrLf & "New CommandText: " & qt.CommandText
        Next
    Next
   
End Sub

We recently migrated data and I have to go through 100+ microsoft query ODBC and update the "DBQ='NEW Server'" and Command Text "FROM'New Server'

-This solution worked when I only have one table in the command text. What can i do if I have 2 or more tables?

 

[John_w]

-This solution worked when I only have one table in the command text. What can i do if I have 2 or more tables?

Just repeat the Replace statement for each table name you need to change, for example:

Public Sub Change_Command_Text()

    Dim ws As Worksheet, lo As ListObject, qt As QueryTable, prev As String
    
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            Set qt = lo.QueryTable
            prev = qt.CommandText
            qt.CommandText = Replace(qt.CommandText, "table1Name.", "newTable1Name.", compare:=vbTextCompare)
            qt.CommandText = Replace(qt.CommandText, "table2Name.", "newTable2Name.", compare:=vbTextCompare)
            MsgBox "Previous CommandText: " & prev & vbCrLf & "New CommandText: " & qt.CommandText
        Next
    Next
   
End Sub

 

[MzCheez]

Just repeat the Replace statement for each table name you need to change, for example:

Public Sub Change_Command_Text()

    Dim ws As Worksheet, lo As ListObject, qt As QueryTable, prev As String
   
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            Set qt = lo.QueryTable
            prev = qt.CommandText
            qt.CommandText = Replace(qt.CommandText, "table1Name.", "newTable1Name.", compare:=vbTextCompare)
            qt.CommandText = Replace(qt.CommandText, "table2Name.", "newTable2Name.", compare:=vbTextCompare)
            MsgBox "Previous CommandText: " & prev & vbCrLf & "New CommandText: " & qt.CommandText
        Next
    Next
  
End Sub

I tried that and I kept getting an error for the Set qt = lo.querytable

Also thank you ? I search for over an hour until I found your solution.