title: Excel VBA; Updating a connection string
body: <p>I'm just trying to get VBA to update an OLEDB connection string. When I step through the code, I don't get any errors but the connection refresh fails and when I examine the connection string in the UI, it's obvious that my code has not changed it at all (hence the refresh failure). What have I missed?</p>
  
  <p>Here is the code:</p>
  
  <pre><code>Sub UpdateQueryConnectionString(ConnectionString As String)
  
    With ActiveWorkbook.Connections("Connection Name"). _
        OLEDBConnection
        .Connection = StringToArray(ConnectionString)
    End With
    ActiveWorkbook.Connections("Connection Name").Refresh
  End Sub
  </code></pre>
  
  <p>The ConnectionString being fed in is:</p>
  
  <pre><code>ConnectionString = = "Provider=SLXOLEDB.1;Data Source=SERVER;Initial Catalog=DATABASE" _
  &amp; ";User ID=" &amp; Username &amp; ";Password=" &amp; Password &amp; _
  ";Persist Security Info=True;Extended Properties=" _
  &amp; Chr(34) &amp; "PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;INCLUDECALCFIELDS=ON;" &amp; Chr(34)
  </code></pre>
  
  <p>The function StringToArray is copied straight out of Example 4 on <a href="http://support.microsoft.com/kb/105416" rel="nofollow">http://support.microsoft.com/kb/105416</a></p>