Re: VBA/Access Global Search&Replace from LUT

[simon.robin.jackson]

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?

[paii, Ron]

<si************ *****@gmail.com wrote in message
news:25******** *************** ***********@r66 g2000hsg.google groups.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?

You stated in the OP that the Excel sheet 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.

[Larry Linson]

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.com wrote in message
news:25******** *************** ***********@r66 g2000hsg.google groups.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?

[paii, Ron]

"Larry Linson" <bo*****@localh ost.notwrote in message
news:hRW%j.24$B Y1.18@trnddc06. ..

Ron has, perhaps, a good deal more patience than many here.

My patience is more self preservation. How many members have had to defend
Access to clients, co-workers, IT consultants, who say Access is junk or a
toy because of databases like the proceeding.

[simon.robin.jackson]

Ron & Larry - good comments, taken on board and I am already thinking
on how to best re-structure the data.

However, this is not a simple Access Database. Its a GeoDatabase
(created from ArcMAP software) and the main front end for the data is
within the ArcMAP software. Bit hard to explain, but it makes
database design a bit limited as there are already a number of
datasets that have been created around this existing database design.

I might post a separate thread if I can come up with a plan/get the
plan approved.

Thanks loads everyone. The script I posted earlier did work for my
short-term requirements, I understand that errors could have crept in,
but I fiddled it to look at only the fields required and if there was
an error, to skip over it.

[Larry Linson]

There are always exceptions that have to be dealt with. It's really helpful
if we know enough detail about the situation to avoid long side-tracks.
Good luck.

Larry

<si************ *****@gmail.com wrote in message
news:88******** *************** ***********@p25 g2000hsf.google groups.com...

Ron & Larry - good comments, taken on board and I am already thinking
on how to best re-structure the data.

However, this is not a simple Access Database. Its a GeoDatabase
(created from ArcMAP software) and the main front end for the data is
within the ArcMAP software. Bit hard to explain, but it makes
database design a bit limited as there are already a number of
datasets that have been created around this existing database design.

I might post a separate thread if I can come up with a plan/get the
plan approved.

Thanks loads everyone. The script I posted earlier did work for my
short-term requirements, I understand that errors could have crept in,
but I fiddled it to look at only the fields required and if there was
an error, to skip over it.