Replace function won't work on Access 2007 when called in SQL using ADODB

Question

Replace function won't work on Access 2007 when called in SQL using ADODB.

Answer 1

Precisely, when called from ADODB through ODBC.

Answer 2

I just ran this test in 2007 SP2:

    Dim cnn As ADODB.Connection

    Dim strSQL As String

    Set cnn = CurrentProject.Connection

    strSQL = "SELECT LastName, Replace(LastName,'e','XX') as XFile " _

            & "INTO zzzTest " _

            & "FROM Employees"

    cnn.Execute strSQL

    Application.RefreshDatabaseWindow

    Set cnn = Nothing

It worked perfectly.

Answer 3

It works with A2010, though:

? CurrentProject.Connection.Execute("SELECT REPLACE('abcdef', 'b', 'z')").Fields(0).Value

azcdef

Answer 4

Replace is no more supported in Access 2007 when called via ADO.

So if you need to do single replacement, handle the same in programing code.

Else if is a mass replacement, you can follow below logic.

LEFT([NAME],INSTR(1,[NAME],'LTD')-1) + 'LIMITED' + MID([NAME],INSTR(1,[NAME],'LTD')+3)

[NAME] being the field  'LTD' being the search string & 'LIMITED' being the replace string.

Drawback of this usage is you have to repeat calls, since each execution will replace only the first occurance of th search string.