Ron has, perhaps, a good deal more patience than many here.
Your client has a flawed database design that makes it difficult to
accomplish a relatively simple task. In fact, it is the type of Access
database that experienced people here class as "committing spreadsheet" --
spreadsheets are very flexible for small amounts of data and manual
manipulations; databases are for structuring your data and automating
handling and manipulations -- an Access DB is not just a "bigger Excel
spreadsheet".
Rather than saying "I have no control, only have to do what is requested,"
you have an opportunity to "be a hero"; personally, I think you have an
obligation to the client to carefully explain in simple terms how the flawed
design will, sooner or later (and probably sooner, like before this project
is complete) "rise up to bite them in the tender places." You can likely
make a good case that restructuring and normalizing the data, then making
the changes, will result in less time/effort/cost that making the changes to
what they have.
I, for one, haven't the patience to review code that encourages persisting
an improperly structured database. I suspect others here will take a
similar view: "fix your database structure first, then deal with the
changes, which in a well-structured database."
In a properly structured database, all those fields in all those tables
would contain a "foreign key", the ID field of a names table, those foreign
key fields would not need to be changed, only the text of the names in the
names table, and, lo, the proper names would appear whereever the foreign
key was used to join to the names table (which should be everywhere you need
to display the name). Not "magic," just proper relational design.
Larry Linson
Microsoft Office Access MVP
<si*****************@gmail.comwrote in message
news:25**********************************@r66g2000 hsg.googlegroups.com...
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?