Exception when trying to execute “REPLACE” against

问题:

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

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

回答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;

回答3:

Not sure if this is related to the problem you are having but I was having a problem running an update that contained a replace function in Access 2010 that would just return with no error - nothing. I was actually running it from OleDb in .NET and finally figured out I needed to set a registry key to turn off "Sandboxed Mode".

http://office.microsoft.com/en-us/access-help/use-sandbox-mode-in-access-2007-HA010167429.aspx

Hope that helps.

回答4:

I confirm "Sandboxed mode" fix the problem with "Replace" function.

Details of sandbox mode : https://support.office.com/en-au/article/Functions-and-properties-in-Access-2007-blocked-by-sandbox-mode-9a829783-f7a8-4a9f-8d43-8650b8cc9565