We help IT Professionals succeed at work.

Loop through each table, then Find & Replace

1,021 Views
Last Modified: 2013-12-20
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
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

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

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

Commented:
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?
Jez WaltersBusiness Data Analyst
CERTIFIED EXPERT

Commented:
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
Jez WaltersBusiness Data Analyst
CERTIFIED EXPERT

Commented:
Bear in mind that the above needs tweaking to replace "CAT" with "DOG" but ignore "CATS" - what's your criteria for data to ignore?
Jez WaltersBusiness Data Analyst
CERTIFIED EXPERT

Commented:
Need to skip system tables/fields too
Jez WaltersBusiness Data Analyst
CERTIFIED EXPERT

Commented:
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 ...
Jez WaltersBusiness Data Analyst
CERTIFIED EXPERT

Commented:
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?
Jez WaltersBusiness Data Analyst
CERTIFIED EXPERT

Commented:
How do you want to handle multiple occurrences?

Author

Commented:
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

Business Data Analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
ok once I add that sub how do I call it?
Jez WaltersBusiness Data Analyst
CERTIFIED EXPERT

Commented:
Given this is a one-off, you may as well just invoke the code from the Microsoft Visual Basic Immediate window.

Author

Commented:
Thank you!

This worked perfectly.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Nice job, Jez ...

mx
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.