Public Function fnFixSlashOnTable(Optional ByVal strTableName As String)
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim db As DAO.Database
Dim strSQL As String
Dim strWildCard As String
On Error GoTo err_handler
Set db = CurrentDb
strWildCard = IIf(strTableName = "", "*", strTableName)
' recurse to all tables in this database
For Each td In db.TableDefs
If Not (td.Name Like "MSys*") Then
strSQL = ""
If td.Name Like strWildCard Then
For Each fld In td.Fields
'recurse to all fields in this table
If fld.type = dbText Or fld.type = dbMemo Then
'if field is Text Or Memo, include it for replacement of "/" with ""
'build our update query string here.
strSQL = strSQL & _
"[" & fld.Name & "]=Iif(Not IsNull([" & fld.Name & "]),Replace([" & fld.Name & "],'/',''),Null),"
End If
Next
If strSQL <> "" Then
'remove extra "," from our query string
strSQL = Left(strSQL, Len(strSQL) - 1)
'add additional text
strSQL = "Update [" & td.Name & "] Set " & strSQL & ";"
'execute the update
'replace "/" with ""
CurrentDb.Execute strSQL, dbFailOnError
End If
End If
End If
Next td
exit_gracefully:
'remove all objects we use
If Not (fld Is Nothing) Then Set fld = Nothing
If Not (td Is Nothing) Then Set td = Nothing
If Not (db Is Nothing) Then Set db = Nothing
Exit Function
err_handler:
MsgBox err.Number & ": " & err.description
Resume exit_gracefully
End Function