Excel query update

I have an Excel spreadsheet that contains several hundred rows and about 50 columns.
Some of these columns are yes/no type.
I have created a query to extract a subset of data based on the value of one of the yes/no columns.
So far so good - everything works and I see the desired subset in another worksheet.
If I move the workbook to a different folder the query does not work as the data paths in the Connection still point to the old location.
I can manually edit the Connection to point to the correct location but I will have 20 or more subsets to extract and the workbook location will change again.
I want to use vba to change the Connection to point to the current file location and refresh the subset on the click of a button.
I have tried using relative file locations but that doesn't work.
I worked out the vba to make the changes but the changes are not being saved.
Have I missed something or am I doing something wrong or stupid?
This is the vba, the two msgbox lines return the same string:

Sub UpdateSubSet()

    Dim strPath As String
    Dim strFile As String
    Dim strConnect() As String
    
    strPath = ThisWorkbook.Path
    strFile = ThisWorkbook.Name
    strConnect = Split("ODBC;DSN=Excel Files;" _
                        & "DBQ=" & strPath & "\" & strFile & ";" _
                        & "DefaultDir=" & strPath & ";" _
                        & "DriverId=1046;" _
                        & "MaxBufferSize=2048;" _
                        & "PageTimeout=5;", ";")
            
    MsgBox ThisWorkbook.Connections("qryGetSubSet").ODBCConnection.Connection
    
    ThisWorkbook.Connections("qryGetSubSet").ODBCConnection.Connection = strConnect
    
    MsgBox ThisWorkbook.Connections("qryGetSubSet").ODBCConnection.Connection
      
    ThisWorkbook.RefreshAll

End Sub