473,325 Members | 2,816 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Re: VBA/Access Global Search&Replace from LUT

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?
Jun 27 '08 #1
5 1599

<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?
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.
Jun 27 '08 #2
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?

Jun 27 '08 #3

"Larry Linson" <bo*****@localhost.notwrote in message
news:hRW%j.24$BY1.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.
Jun 27 '08 #4
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.
Jun 27 '08 #5
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.comwrote in message
news:88**********************************@p25g2000 hsf.googlegroups.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.

Jun 27 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Zenobia | last post by:
Hello I want a search & replace text in source code for several files in several directories. It would seem that both Dreamweaver MX 6 and GoLive 6 offer this feature but not for .aspx file...
3
by: tchurm | last post by:
Hi Pythonistas, Here's my problem: I'm using a version of MOOX Firefox (http://moox.ws/tech/mozilla/) that's been modified to run completely from a USB Stick. It works fine, except when I...
1
by: Tomomichi Amano | last post by:
Could some one tell me how I can seach and replace only one word in a textBox (THE FIRST WORD THAT COMES AFTER THE CURSOR). I already know how to replace ALL , but I don't know how to REPLACE one,...
1
by: Tomomichi Amano | last post by:
Hello. I want to make replace & search functions in my text editor. Thanks to the kind people here at the newsgroup, I was able to make the function. But I was not able to understand how to...
2
by: Jan | last post by:
Hello! I am looking for a way to do a search&replace in ASCII-Files by a vb.net 2005 programm. Of coarse I can open the files, loop to every line, make a replace, and save the line. But I wonder...
6
by: DataSmash | last post by:
Hello, I need to search and replace 4 words in a text file. Below is my attempt at it, but this code appends a copy of the text file within itself 4 times. Can someone help me out. Thanks! #...
2
by: Ola K | last post by:
Hi guys, I wrote a script that works *almost* perfectly, and this lack of perfection simply puzzles me. I simply cannot point the whys, so any help on it will be appreciated. I paste it all here,...
6
by: simon.robin.jackson | last post by:
Ok. I need to develop a macro/vba code to do the following. There are at least 300 corrections and its expected for this to happen a lot more in the future. Therefore id like a nice...
0
by: simon.robin.jackson | last post by:
I can open the tables into Excel no problem and rekon i could macro/ vba it in excel a bit easier. However, this is a copy of the data I think? not the actual source data from the .mdb. Is...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.