Exception when trying to execute "REPLACE" against MS Access

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.

Solution 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;