Link to home
Start Free TrialLog in
Avatar of mgcIT
mgcITFlag for United States of America

asked on

Loop through each table, then Find & Replace

I have an MS Access Database and I am looking for a way to do a global Find & Replace ( or UPDATE statement).  I have several tables, each with various amounts of rows, columns.  I want to do a Find & Replace for every table, and every column

so that if I find a value of "CAT" anywhere in the database I can change it to "DOG" for an example.  I want it to match exactly so that something like "CATS" would NOT be changed

Note: I don't want to change Table names, just data within the tables.  I'm guessing I need some sort of VB Script to do this
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

The best there is:

http://www.rickworld.com/products.html#Find%20and%20Replace%209.0

Get it ... pays for itself in the first use.

mx
btw ... I use it most every day ... and have been for 15 years!

mx
ok ... I missed the 'data' part.  Wherein you *can* do that with F&R using the Custom search feature, to do it in every table and every column would require a LOT of setup.

mx
Avatar of mgcIT

ASKER

this is just a one-time thing so i don't want to spend a lot of time setting it up.

I can manually open each table, do CTRL + H and specify to look in the entire table and do a find & replace.  any way to automate this with a script or macro so I don't have to open each table manually?
This should get you going:

Public Sub ReplaceValues(pstrSearch As String, pstrReplace As String)

  Dim dbsDatabase As Database
  Dim tdfTableDef As TableDef
  Dim rstRecordset As Recordset
  Dim fldField As Field

  Set dbsDatabase = CurrentDb
  For Each tdfTableDef In dbsDatabase.TableDefs
    Set rstRecordset = dbsDatabase.OpenRecordset(tdfTableDef.Name, dbOpenTable)
    Do Until rstRecordset.EOF
      rstRecordset.Edit
      For Each fldField In rstRecordset.Fields
        rstRecordset(fldField.Name) = Replace(rstRecordset(fldField.Name), pstrSearch, pstrReplace, , , vbBinaryCompare)
      Next
      rstRecordset.Update
      rstRecordset.MoveNext
    Loop
    rstRecordset.Close
    Set rstRecordset = Nothing
  Next

End Sub
Bear in mind that the above needs tweaking to replace "CAT" with "DOG" but ignore "CATS" - what's your criteria for data to ignore?
Need to skip system tables/fields too
Here's how to skip system tables/fields:

Public Sub ReplaceValues(pstrSearch As String, pstrReplace As String)

  Dim dbsDatabase As Database
  Dim tdfTableDef As TableDef
  Dim rstRecordset As Recordset
  Dim fldField As Field

  Set dbsDatabase = CurrentDb
  For Each tdfTableDef In dbsDatabase.TableDefs
    If (tdfTableDef.Attributes And dbSystemObject) = 0 Then
      Set rstRecordset = dbsDatabase.OpenRecordset(tdfTableDef.Name, dbOpenTable)
      Do Until rstRecordset.EOF
        rstRecordset.Edit
        For Each fldField In rstRecordset.Fields
          If (fldField.Attributes And dbSystemField) = 0 Then
            rstRecordset(fldField.Name) = Replace(rstRecordset(fldField.Name), pstrSearch, pstrReplace, , , vbBinaryCompare)
          End If
        Next
        rstRecordset.Update
        rstRecordset.MoveNext
      Loop
      rstRecordset.Close
      Set rstRecordset = Nothing
    End If
  Next
End Sub

So that just leaves your replacement criteria ...
Is this what you need; instead of:

  rstRecordset(fldField.Name) = Replace(rstRecordset(fldField.Name), pstrSearch, pstrReplace, , , vbBinaryCompare)

have this instead:

  If rstRecordset(fldField.Name) = pstrSearch Then
    rstRecordset(fldField.Name) = pstrReplace
  End If

Any thoughts on capitalisation?
How do you want to handle multiple occurrences?
Avatar of mgcIT

ASKER

hi, thanks for the replies.

I want it to be case sensitive, and match the entire field (doing a manual find & replace there is a drop-down box to specify Match: Whole Field)

so only a field with exactly: CAT

not:

CATS
cat
CATCAT

ASKER CERTIFIED SOLUTION
Avatar of Jez Walters
Jez Walters
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mgcIT

ASKER

ok once I add that sub how do I call it?
Given this is a one-off, you may as well just invoke the code from the Microsoft Visual Basic Immediate window.
Avatar of mgcIT

ASKER

Thank you!

This worked perfectly.