I'm trying to execute an SQL query against a MS Access database containing a "REPLACE" function:

UPDATE MyTable 
   SET MyColumn = REPLACE(MyColumn, 'MyOldSubstring', 'MyNewSubstring') 
 WHERE Id = 10;

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.

The exception:

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& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       ...

Why do I get this exception?

More info:

  • My application is a WPF application
  • I'm using .NET 3.5
  • I run MS Access 2007
  • My connectionstring is "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyFolder\MyDatabase.accdb"

My database access code looks something like this, where I'll just pass in the mentioned SQL as a string:

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(
Edit
Report