Searching & Replacing in Access

Sometimes you need to do more advanced searches (and replaces) on your data than the access interface will allow. This article shows a generic function which can help!

---

Start by adding a new Module to your database. Open the module in the code view window, and then go to tools/references; you’ll need to add a reference to the “Microsoft ActiveX Data Objects 2.x Library” (I currently use 2.5, although you can use later versions if you prefer).

Next, paste this code into the module:

`Option Compare Database
Option Explicit

Sub testfunction()
SearchAndReplaceAField “tblTours”, “TourID”, “DY%1#19.000000″ ‘, “DY%1$Cab.DZ – 5″
End Sub

Sub SearchAndReplaceAField(strTable As String, strField As String, strFind As String, Optional strReplace As String)
Dim con As New ADODB.Connection
Set con = Application.CurrentProject.Connection
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim fld As ADODB.Field, strFileName As ADODB.Field
Dim stsql As String

stsql = “SELECT * FROM ” & strTable

rs.Open stsql, con, adOpenKeyset, adLockOptimistic
Set fld = rs.Fields(strField)

If (rs.EOF) Then
Debug.Print “No Records Found.”
Else

While Not rs.EOF
If InStr(1, fld.Value, strFind, vbBinaryCompare) Then
If IsNull(strReplace) Or strReplace = “” Then
Debug.Print “Found ‘” & strFind & “‘ in: ‘” & fld.Value & “‘”
Else
Debug.Print “Replaced ‘” & strFind & “‘ in: ‘” & fld.Value & “‘”
rs.Fields(strField) = Replace(fld.Value, strFind, strReplace)
rs.Update
End If
End If
rs.MoveNext
Wend
End If

‘ Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing
End Sub`

The first function is nothing more than a way to launch the second function, with all parameters filled in.

The second function is a very generic function, which, by the use of parameters, can be called to search and optionally replace data from any table in the database.

You may notice that Instr is using the “vbBinaryCompare” option; you may need this if the data you are searching has been encoded in any way.

If you find this useful, leave me a comment!

– Gon