''
'' arnelgp
''
'' purpose
''
'' replace old string with new string on all table on all fields
''
''
'' parameters
''
'' SearchString = the string you want to be replaced
'' ReplacementString = of course, the new string replacement
''
''
'' calls fnAnySQL() function
''
Option Compare Database
Option Explicit
Public Function ReplaceAnyField(SearchString As String, ReplacementString As String)
Dim td As DAO.TableDef
Dim db As DAO.Database
Dim strAction As String
Dim fld As DAO.Field
Dim arrFields As Variant
Dim strField As String
Dim lngCount As Long
Dim strTable As String
Dim var As Variant
Set db = CurrentDb
DoCmd.Hourglass True
For Each td In db.TableDefs
strField = ""
If InStr(td.Name, "~") = 0 And InStr(td.Name, "Msys") = 0 Then
strTable = "[" & td.Name & "]"
For Each fld In td.Fields
'only fields that are not multivalues are included
If fld.type < 101 Then
lngCount = DCount("[" & fld.Name & "]", strTable, _
"instr([" & fld.Name & "]," & Chr(34) & SearchString & Chr(34) & ")>0")
If lngCount > 0 Then _
strField = strField & ",[" & fld.Name & "]"
End If
Next
End If
If strField <> "" Then
strAction = ""
arrFields = Split(Mid(strField, 2), ",")
For Each var In arrFields
strAction = "Update " & strTable & " Set " & var & "=Replace(" & var & ", @p1, @p2)" & _
" Where InStr(" & var & ", @p1)>0"
Call fnAnySQL(strAction, SearchString, ReplacementString)
DoEvents
Next
End If
DoEvents
Next
Set db = Nothing
DoCmd.Hourglass False
End Function
Public Function fnAnySQL(ByVal strSQL As String, ParamArray p() As Variant)
'
' strSQL must be an Any SQL statement (Select, Insert, Update, Delete)
' parameters should be in format @1, @2, ... to be
' consistent
'
' DO NOT use parameter name which has same name as your fieldname.
' using this will take the fieldname instead of the parameter.
'
' Example:
'
' SELECT Statement:
' Dim rs As DAO.Recordset
' set rs = fnAnySQL("SELECT field1 FROM table1 WHERE Names = @1;", "arnelgp")
'
'
' UPDATE Statement:
' Call fnAnySQL("Update table1 Set field1 = @1;", "arnelgp")
'
'
' DELETE Statement:
' Call fnAnySQL("Delete table1.* From table1 Where field1 = @1;", "arnelgp")
'
' INSERT Statement:
' Call fnAnySQL("Insert Into table1 (field1, field2) SELECT @1, @2;", "arnel", "gp")
'
'
Dim param As Parameter
Dim i As Integer
With CurrentDb.CreateQueryDef("", strSQL)
For i = 0 To .Parameters.Count - 1
.Parameters(i) = p(i)
Next
If InStr(strSQL, "SELECT") = 1 And InStr(strSQL, "INTO") = 0 Then
' Simple select query and not Create table query
Set fnAnySQL = .OpenRecordset(dbOpenDynaset)
Else
' Action queries
.Execute (dbFailOnError)
End If
End With
End Function
