Re: VBA/Access Global Search&Replace from LUT


<simon.robin.jackson@xxxxxxxxx> wrote in message
news:2594bcab-4a4e-4b3c-9de0-604357467cd3@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This requires a table called tblReplace with two text fields, old and
new.

Code:
On Error Resume Next

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim f As DAO.Field
Set db = CurrentDb
For Each td In db.TableDefs
If td.Name <> "tblReplace" And Left(td.Name, 4) <> "msys" Then
For Each f In td.Fields
SQL = "UPDATE [" & td.Name & "] INNER JOIN tblReplace ON
[" & _
td.Name & "].[" & f.Name & "] = tblReplace.old SET ["
& _
td.Name & "].[" & f.Name & "] = [tblReplace]![new];"
db.Execute SQL
Select Case Err.Number
Case 0
Case 3615: Err.Clear
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
Exit Sub
End Select
Next
End If
Next


Anyone disagree?

You stated in the OP that the Excel *** contains records where the new
name is blank. Assuming those records should not be updated, modify your
query to filter them out.

It looks like you are linking every field in every table to tblReplace and
updating any matching record. You will likely get errors on every run for
every table, so you will need to be at the computer clicking OK to each
message. Understand that you as you update the data is inconsistent until
all 30 tables are done and you can't use transactions because of the errors.
Try it on a test copy and see how long it takes to run and how your data
looks afterwards.

I would think running it in Access with linked tables would be better, but
it can be done in Excel.


.

Quantcast