MS Access - Search all fields for a character and replace it with another

I have a VBA module that exports a number of tables in a CSV format. The end user now needs these files to be in a pipe deliminted format "|" or ASCII. I have the following code which works fine:

Dim vbpipe As String
vbpipe = Chr(124)
Debug.Print vbpipe

I can then use vbpipe to delimit the output. Prior to doing that, I would like to search all fields in the table for the "|" character and replace any existing pipes with a "~" tilde, so that my outputs will be clean. What I am trying to avoid is specifying each field in each table and searching and replacing individually.

I found the code below on the web and it is close, but not exactly what I want. I don't want to do an entire database, and I only want to replace one character, not two. I do not understand how to use arrays properly, so I did not want to try to play with this code, but it might be a helpful starting point.

Private Sub fixCharacters()
Dim rst As Recordset, fld As Field
Dim varCharsIn As Variant, varCharsOut As Variant
Dim strSplit() As String
Dim i As Integer
Rem set up an array of characters to be replaced
varCharsIn = Array("è", "§")
Rem set up array of replacement characters - same relative positions as above array
varCharsOut = Array("c", "z")
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [table];")
Do Until rst.EOF
  For Each fld In rst.Fields
    Select Case fld.Type
    Rem do this for text fields only
    Case dbText
         Rem ensure field is not null
         If Nz(fld, vbNullString) <> vbNullString Then
           Rem step through the input array to check for matches in field
           For i = 0 To UBound(varCharsIn)
             Rem split on the target character
             strSplit = Split(fld, varCharsIn(i))
             Rem if the split array has only one dimension,there's no match
             If UBound(strSplit) > 0 Then
               Rem found a match
               Rem rejoin the array using the corresponding replacement character
               rst.Fields(fld.Name) = Join(strSplit, varCharsOut(i))
             End If
         End If
    Case Else
    End Select
End Sub
Rick RudolphAsked:
Who is Participating?
Rey Obrero (Capricorn1)Commented:
test this code

Sub fixCharacters()
Dim rst As Recordset, fld As Field, strTable As String
strTable = "TableName"    '<< CHANGE WITH ACTUAL NAME OF TABLE
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [" & strTable & "];")
  For Each fld In rst.Fields
    Select Case fld.Type
    Case dbText
        CurrentDb.Execute "update [" & strTable & " ] set  [" & fld.Name & "] = replace([" & fld.Name & "],'|','~')"
    Case Else
    End Select
End Sub
Rick RudolphAuthor Commented:
Very Clean, works perfectly
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.