Exception when trying to execute "REPLACE" against MS Access

Question

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();
    }
}

(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...)

Alternative solution?

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...

Answer 1

In interactive Access, the Access Expression Service takes care of providing you access to user-defined and VBA functions, but the Access Expression Service is not available from outside Access. When accessing Jet/ACE data via ODBC or OLEDB, only a limited number of functions are available. Replace() is not one of them. However, you may be able to use InStr() and Len() to replicate the functionality of the Replace() function, but it would be fairly ugly.

Answer 2

it is impossible to get the REPLACE to work, maybe you know of some alternative solution?

Here's the "fairly ugly" alternative approach alluded to by @David-W-Fenton:

UPDATE MyTable 
   SET MyColumn = MID(
                      MyColumn, 
                      1, 
                      INSTR(MyColumn, 'MyOldSubstring') 
                         - 1
                     ) 
                     + 'MyNewSubstring'
                     + MID(
                           MyColumn, 
                           INSTR(MyColumn, 'MyOldSubstring') 
                              + LEN('MyOldSubstring'), 
                           LEN(MyColumn) 
                              - INSTR(MyColumn, 'MyOldSubstring') 
                              - LEN('MyOldSubstring')
                              + 1
                          )
 WHERE INSTR(MyColumn, 'MyOldSubstring') > 0
       AND Id = 10;