9

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

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
haagel
  • 2,646
  • 10
  • 36
  • 53

4 Answers4

6

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.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • I don't have a solution (I don't ever work outside of Access!), but I was hoping that pointing this out might lead to an answer from someone who could help, perhaps someone willing to hack together the ugly expression required using InStr() and Len() to do the job. – David-W-Fenton Feb 09 '11 at 03:33
  • 1
    "perhaps someone willing to hack together the ugly expression required" -- done! ;) – onedaywhen Feb 09 '11 at 11:58
5

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;
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • 1
    The question about this is how intelligently the Jet/ACE query optimizer is in interacting with the Jet expression service. That is, will it evaluate `INSTR(MyColumn, 'MyOldSubstring')` and `LEN('MyOldSubstring')` each time it encounters it, or will it evaluate once and cache the result? I know that the Jet/ACE query optimizer is smart with "vertical" optimization, i.e., evaluating only once any expression that is the same for all rows, but I worry that it's not that smart with "horizontal" optimization, i.e., evaluating the same expression only once per row. [continued...] – David-W-Fenton Feb 11 '11 at 22:50
  • I would expect that if you had two calculated columns `Len(MyField) AS Length1` and `Len(MyField) + 3 As Length2` that it would be evaluated only once. But when the expressions are arguments passed to another function, I worry that they won't be evaluated efficiently. Unfortunately, the interaction with the Jet/Access expression services is not documented in SHOWPLAN, so there's no real way to know. You could evaluate how calls to the Access expression service are optimized by writing UDFs that output with Debug.Print each time they are called, but you can'd to that with non-UDFs. – David-W-Fenton Feb 11 '11 at 22:54
  • Nice job, BTW. Of course, it's just a SQL implementation of the UDFs we had to write before Access got a Replace() funciton (A2000), but it's still messy (because of all the repetition). – David-W-Fenton Feb 11 '11 at 22:56
  • this has to be on the access procedure ? – safi Mar 03 '11 at 10:18
  • @safi: your question is not clear. What do you mean "on the access procedure"? It's SQL that ought to be executable from outside Access via ADO/OLEDB or ODBC because all the functions it uses are available to the Jet/ACE expression service. – David-W-Fenton Mar 04 '11 at 04:10
  • How can I use this in a where – Boyen Jul 23 '14 at 17:34
  • @Boyen: in your case (i.e. removing all spaces) the approach shown here wouldn't be practical. The advice to update to ACE is good. – onedaywhen Aug 07 '14 at 11:01
0

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

Vick Airfull
  • 1
0

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.

Ryan
  • 1