vba - Modify an embedded Connection String in microsoft excel macro

Question

I have an Excel document that has a macro which when run will modify a CommandText of that connection to pass in parameters from the Excel spreadsheet, like so:

Sub RefreshData()
ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary")
  .OLEDBConnection.CommandText = "Job_Cost_Code_Transaction_Summary_Percentage_Pending @monthEndDate='" & Worksheets("Cost to Complete").Range("MonthEndDate").Value & "', @job ='" & Worksheets("Cost to Complete").Range("Job").Value & "'"
ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").Refresh
End Sub

I would like the refresh to not only modify the connection command but also modify the connection as I would like to use it with a different database also:

Just like the macro replaces the command parameters with values from the spreadsheet I would like it to also replace the database server name and database name from values from the spreadsheet.

A complete implementation is not required, just the code to modify the connection with values from the sheet will be sufficient, I should be able to get it working from there.

I tried to do something like this:

 ActiveWorkbook
 .Connections("Job_Cost_Code_Transaction_Summary")
 .OLEDBConnection.Connection = "new connection string"

but that does not work. Thanks.

Answer 1

The answer to my question is below.

All of the other answers are mostly correct and focus on modifying the current connection, but I want just wanting to know how to set the connection string on the connection.

The bug came down to this. If you look at my screenshot you will see that the connection string was:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ADCData_Doric;Data Source=doric-server5;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LHOLDER-VM;Use Encryption for Data=False;Tag with column collation when possible=False

I was trying to set that string with ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.Connection = "connection string"

I was getting an error when i was simply trying to assign the full string to the Connection. I was able to MsgBox the current connection string with that property but not set the connection string back without getting the error.

I have since found that the connection string needs to have OLEDB; prepended to the string.

so this now works!!!

ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ADCData_Doric;Data Source=doric-server5;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LHOLDER-VM;Use Encryption for Data=False;Tag with column collation when possible=False"

very subtle but that was the bug!