title: Exception when trying to execute "REPLACE" against MS Access
body: <p>I'm trying to execute an SQL query against a MS Access database containing a "REPLACE" function:</p>
  
  <pre><code>UPDATE MyTable 
     SET MyColumn = REPLACE(MyColumn, 'MyOldSubstring', 'MyNewSubstring') 
   WHERE Id = 10;
  </code></pre>
  
  <p>If I run this query from inside MS Access (the application) it works fine. But when I try to run it from my application an exception is thrown.</p>
  
  <p>The exception:</p>
  
  <pre><code>System.Data.OleDb.OleDbException was unhandled
    Message="Undefined function 'REPLACE' in expression."
    Source="Microsoft Office Access Database Engine"
    ErrorCode=-2147217900
    StackTrace:
         at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
         at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp; executeResult)
         at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp; executeResult)
         at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp; executeResult)
         at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
         at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
         ...
  </code></pre>
  
  <p><strong>Why do I get this exception?</strong></p>
  
  <p>More info:</p>
  
  <ul>
  <li>My application is a WPF application</li>
  <li>I'm using .NET 3.5 </li>
  <li>I run MS Access 2007</li>
  <li>My connectionstring is "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyFolder\MyDatabase.accdb"</li>
  </ul>
  
  <p>My database access code looks something like this, where I'll just pass in the mentioned SQL as a string:</p>
  
  <pre><code>public void ExecuteNonQuery(string sql)
  {
      OleDbCommand command = new OleDbCommand(sql);
      OleDbConnection connection = new OleDbConnection(ConnectionString);
      command.Connection = connection;
  
      try
      {
          connection.Open();
          command.ExecuteNonQuery();
      }
      catch
      {
          throw;
      }
      finally
      {
          connection.Close();
      }
  }
  </code></pre>
  
  <p><em>(Some code like error handling removed for brevity. Observe that I'm only building a quick prototype so this plumbing code is never going to be used for real, so please bear with it. ;) I still need this to work though...)</em></p>
  
  <p><strong>Alternative solution?</strong></p>
  
  <p>If it is impossible to get the REPLACE to work, maybe you know of some alternative solution? I could fetch all the rows i want to update , do this string replace in code and then update the rows in the database. But that could be a lot of SQL queries (one to fetch and one for each row to update) and wouldn't be a very elegant solution...</p>