We import XML files into an Access database. This data includes a description column which can contain special characters like ä é etc. We use this database to export a table to our Financial programm. This programm can't handle these special characters. Therefore I would like to make a VBA function that can replace any special character within a specific table/colomn.
I am fairly new to using VBA, so I used Google a lot to try to find some background about this topic. I have found this code for Excel, and think this can be used in Access as well. However, I can't make the connection with the Update table function.
Function RemovePunctuation(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "[^A-Z0-9 ]"
.IgnoreCase = True
.Global = True
RemovePunctuation = .Replace(Txt, "")
End With
End Function
The Function above (RemovePunctuation) works with the Query in Access and returns a list with the values of the original table without any punctuation. I used the following Query:
SELECT RemovePunctuation([ColumnName]) AS [Add]
FROM TableName;
However, this returns a new table instead of replacing the values in the original table. Can information be shared on a different Query which uses the function to update originale table?
Cause I'm really new to this, I can't show much. I would expect the code to look like:
Function UpdateTable(Table As String, Column As String) As String
Update Table Set Column =
With CreateObject("VBScript.RegExp")
.Pattern = "[^A-Z0-9 ]"
.IgnoreCase = True
.Global = True
RemovePunctuation = .Replace(Txt, "")
End With
End Function
But this returns nothing.
Expected result should be returning spaces where special characters are in column selected.
As mentioned, code returns nothing so far as this isn't a valid function at all. Please advise.
Based on code provided by @jeroen jong, the question is answered. Provided code to replace special characters in any given table in Access is the following:
One module is made with the following code:
Option Compare Database
Option Explicit
Private Const strObject As String = "modConversion"
Function ReplaceString(strCaller As String, memText As Variant, strSearch As String,
strReplace As String) As Variant
On Error GoTo Err_Function
'Define variables
Dim strProcedure As String 'name of current procedure
Dim dblPos As Double 'pointer in text
'Initialise variables
strProcedure = "ReplaceString"
'Walk through the text, search and replace
dblPos = InStr(memText, strSearch)
Do While dblPos > 0
If Asc(strSearch) = Asc(Mid$(memText, dblPos, 1)) Then
memText = Left$(memText, dblPos - 1) + strReplace + Mid$(memText, dblPos + Len(strSearch))
dblPos = Abs(dblPos - Len(strSearch))
End If
dblPos = InStr(dblPos + 1, memText, strSearch)
Loop
ReplaceString = memText
Exit_Function:
Exit Function
Err_Function:
MsgBox Err.Number, Err.Description, Err.Source, strObject, strProcedure
ReplaceString = memText
Resume Exit_Function
End Function
One function is created the call the ReplaceString function. In this case it is called from a Form with a button:
Option Compare Database
Option Explicit
Private Const strObject As String = "frmReplace"
Private Sub cmdReplace_Click()
On Error GoTo Err_Sub
'Define variables
Dim strProcedure As String 'name of current procedure
Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsReplace As DAO.Recordset
Dim strFieldName As String
'Initialise variables
strProcedure = "cmdReplace_Click"
'Initialise database and recordset
Set dbs = CurrentDb
Set rsReplace = dbs.OpenRecordset("tblReplace", dbReadOnly)
With rsReplace
Do While Not .EOF
'Open table
Set rsTable = dbs.OpenRecordset(!TableName, dbOpenDynaset)
'Walk through all records, and replace char in field
Do While Not rsTable.EOF
rsTable.Edit
rsTable(!FieldName) = ReplaceString(strProcedure, rsTable(!FieldName), !TextSearch, !TextReplace)
rsTable.Update
rsTable.MoveNext
Loop 'rsTable
.MoveNext
rsTable.Close
Loop 'rsReplace
.Close
End With
MsgBox "Replacement of special characters is completed", vbInformation, "Replace"
Exit_Sub:
On Error Resume Next
rsTable.Close
Set rsTable = Nothing
rsReplace.Close
Set rsReplace = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub
Err_Sub:
MsgBox Err.Number & " - " & vbLf & Err.Description & " - " & vbLf & Err.Source, vbCritical, strObject & "-" & strProcedure
Resume Exit_Sub
End Sub
The table to provide data to search and replace consists of the following columns:
Id As Id;
TableName As String;
FieldName As String;
Replace As Boolean;
TextSearch As String;
TextReplace As String;
CaseSensitive As Boolean;
Again thanks for solving my question!
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments