mgcIT
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
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
btw ... I use it most every day ... and have been for 15 years!
mx
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
mx
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?
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( tdfTableDe f.Name, dbOpenTable)
Do Until rstRecordset.EOF
rstRecordset.Edit
For Each fldField In rstRecordset.Fields
rstRecordset(fldField.Name ) = Replace(rstRecordset(fldFi eld.Name), pstrSearch, pstrReplace, , , vbBinaryCompare)
Next
rstRecordset.Update
rstRecordset.MoveNext
Loop
rstRecordset.Close
Set rstRecordset = Nothing
Next
End Sub
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(
Do Until rstRecordset.EOF
rstRecordset.Edit
For Each fldField In rstRecordset.Fields
rstRecordset(fldField.Name
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( tdfTableDe f.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(fldFi eld.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 ...
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(
Do Until rstRecordset.EOF
rstRecordset.Edit
For Each fldField In rstRecordset.Fields
If (fldField.Attributes And dbSystemField) = 0 Then
rstRecordset(fldField.Name
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(fldFi eld.Name), pstrSearch, pstrReplace, , , vbBinaryCompare)
have this instead:
If rstRecordset(fldField.Name ) = pstrSearch Then
rstRecordset(fldField.Name ) = pstrReplace
End If
Any thoughts on capitalisation?
rstRecordset(fldField.Name
have this instead:
If rstRecordset(fldField.Name
rstRecordset(fldField.Name
End If
Any thoughts on capitalisation?
How do you want to handle multiple occurrences?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Thank you!
This worked perfectly.
This worked perfectly.
Nice job, Jez ...
mx
mx
http://www.rickworld.com/products.html#Find%20and%20Replace%209.0
Get it ... pays for itself in the first use.
mx