Why setting OLEDBConnection.Connection throws Exception (HRESULT: 0x800A03EC)?

Question

I am working with Excel Interop namespace to modify connection string in the Excel Workbook connections.

Why when I am trying to set the connection property (MSDN OLEDBConnection.Connection) throws an error on the line of the assignment?

Exception from HRESULT: 0x800A03EC

application = new Application();
Workbook wb = application.Workbooks.Open(file.FullName);
Sheets wbs = wb.Worksheets;
IEnumerable<Workbook> workbooks = application.Workbooks.Cast<Workbook>();

foreach (var connection in wb.Connections.Cast<WorkbookConnection>()
    .Where(c => c.Type == XlConnectionType.xlConnectionTypeOLEDB))
{
    connection.OLEDBConnection.Connection = "Test Connection String";
}

application.Quit();

However, calling Replace method as shown below is working. I have found this as workaround, not being sure why Replace works in this case.

application = new Application();
Workbook wb = application.Workbooks.Open(file.FullName);
Sheets wbs = wb.Worksheets;
IEnumerable<Workbook> workbooks = application.Workbooks.Cast<Workbook>();

foreach (var connection in wb.Connections.Cast<WorkbookConnection>()
    .Where(c => c.Type == XlConnectionType.xlConnectionTypeOLEDB))
{
    var conString = connection.OLEDBConnection.Connection.ToString();
    connection.OLEDBConnection.Connection = 
        conString.Replace("Test Connection String", "New Test Connection String");
}

application.Quit();

This in fact is the only way I could get the connection string changed, therefore asking what is the reason behind why set could be throwing the error.

Answer 1

Issue was that connection string seem to have special prefix (OLEDB;) which is checked upon the assignment so it can be solved by this:

...
connection.OLEDBConnection.Connection = "OLEDB;Test Connection String";
...

Prefix showed its face upon connection string retrieval, thus the clue.

Though recommend OpenXML due to ease of testability.