Replace specific characters into another one, in a table field (ADO)

[bulbisi]

Hello all,

I found this on this forum but it is a DAO version and my employer desactivated the DAO (they want now the very last version). And moreover I'm not sure how to convert it into ADO.

Function ReplaceAccents(strTextToReplace As String, strTextToUse As String)
Dim qdf As DAO.QueryDefs
Dim strSQL As String
 
For Each qdf In CurrentDb.QueryDefs
     strSQL = qdf.SQL
        If InStr(1, strTextToReplace, strSQL) > 0 Then
           strSQL = Replace(strSQL, strTextToReplace, strTextToUse)
           qdf.SQL = strSQL
           qdf.Close
        End If
Next
End Function

My wish today (between 2 sneezings):
convert all characters É and È into a E (the same without accent)
The field I need to lookup at is in a table.

Example (this is a french language address not supported by our server, so why.)
RUE DE LA LIBÉRATION 5
should be
RUE DE LA LIBERATION 5

table = "details", field = "address"
in the same DB

Looked simple but... hey. Sorry

So has anyone can share a solution? This function/module will be activated through a menu or a form button.

thanks in advance!
Chris

 

[bulbisi]

Note:

I could use an Update Query like this

UPDATE T_CE_RP_PROPOSAL SET DebAdrTxt = Replace([DebAdrTxt],"È","E");

but that character is not the only one. So it is gonna be a pain

(List :
Á À Ä Â Å Ã => A
É È Ë Ê => E
Í Ì Ï Î => I
Ó Ò Ö Ô Õ => O
Ú Ù Ü Û => U
N° => "" (empty space)
´ => ""
` => ""
° => ""
Š => S
Ž =>Z
Ç => C
)

 

[DCrake]

Here is a custom function that converts accented characters to English equivelants

Function ConvertAccent(ByVal inputString As String) As String

Const AccChars As String = _
    "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
Const RegChars As String = _
    "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"

Dim i As Long, j As Long
Dim tempString As String
Dim currentCharacter As String
Dim found As Boolean
Dim foundPosition As Long

  tempString = inputString

  ' loop through the shorter string
 Select Case True
    Case Len(AccChars) <= Len(inputString)
      ' accent character list is shorter (or same)
     ' loop through accent character string
     For i = 1 To Len(AccChars)

        ' get next accent character
       currentCharacter = Mid$(AccChars, i, 1)

        ' replace with corresponding character in "regular" array
       If InStr(tempString, currentCharacter) > 0 Then
          tempString = Replace(tempString, currentCharacter, Mid$(RegChars, i, 1))
        End If

      Next i
    Case Len(AccChars) > Len(inputString)
      ' input string is shorter
     ' loop through input string
     For i = 1 To Len(inputString)

        ' grab current character from input string and
       ' determine if it is a special char
       currentCharacter = Mid$(inputString, i, 1)
        found = (InStr(AccChars, currentCharacter) > 0)

        If found Then

          ' find position of special character in special array
         foundPosition = InStr(AccChars, currentCharacter)

          ' replace with corresponding character in "regular" array
         tempString = Replace(tempString, currentCharacter, _
    Mid$(RegChars, foundPosition, 1))

        End If
      Next i
  End Select

  ConvertAccent = tempString
End Function

 

[bulbisi]

this looks what I need
it is just that I don't get where to put the table name and the desired to-be-changed field name ?

 

[DCrake]

Create a query with the table that contains the accented pharses

Col1:OriginalFieldName
Col2:ConvertAccent(OriginalFieldName)

Show in datasheet view and compare columns

 

[bulbisi]

hello,
thanks
at a first sight, it is not only changing one field in one query/table... but the whole DB?
Am I right?
I just wrote a couple of Update queries but it is quite heavy and slow.
If I am able to use your clever script, ... well, I would love to!

 

[DCrake]

The way you employ this function will detemine which records and which fields are updated.

It is an intensive function that will take time if it is dealing with hundreds of records. But I suspect that this is not going to be a function that is called frequently. However the time it takes to do it automatically far outweighs the manual exercise.

You are ok to the use the function, I would not have posted the solution if I did not want you to use it.

How have you used it so far?

 

[bulbisi]

At this point We have a use of it twice (!) every month, but for hundreds of lines (soon thousands).
But I'm sure your script is gonna be useful for another DB we have. Unfortunately we cannot change them all straight from the start.
And so today I'll thank you for the code because I know we can use it later, and other users here will be happy too