Find and replace in whole database

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have a database that records employee details. Each employee is identified
by 4 letters. The first 3 letters from their last name and the first letter
from their first name.
This system has proven to be sufficiently robust to use this field as a
primary key in several tables
As it is a primary key there isn't any one place that is looked up.
Occasionally, someone gets married and changes their name.

Is their a way to cycle through all tables in the database, finding a value
and replacing it with another.
I know I can do it with one table with an update query, but what about
several tables at once.

I should add that the way this database has evolved its current design may
be far from ideal, buts thats the problem ... it evolved, rather than
instantly created from a good design.
 
One way would be if you could enforce referential integrity and updates
across all records that need to receive this one update, then you could
change the name in the one field to have it updated across all applicable
locations. Otherwise, my only other thought is to determine table
dependecies and perform the same update on each table that contains that
record. Probably won't be possible in your "normal" forms, because it may
cause record connections to be broken and give some form of an error.
 
Have you set up relationships among the tables? If not, then you just need to
have an update query for each table.

If you have relationships set up and referential integrity turned on. The
process is much more complicated. UNLESS, you turn on the cascade updates
option. This setting will automatically update the foreign key when the
primary key is changed.

The problem is referential integrity. You can't replace the key value in the
child records until you have a record in the parent table with the new key
value. So
== you have to create a new record in the parent table that is a duplicate of
the existing record except it has the new primary key value
== Then you have to update all the child tables with the new key value.
== Finally you can delete the "old" record in the parent table.

Your primary key fails to meet at least two attributes for a primary key. It
is not stable (changes with marriage, divorce, or legal name changes) and is
very likely not to be unique. In my immediate office (40 people) I end up
with two sets of duplicates using that scheme.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I have a database that records employee details. Each employee is identified
by 4 letters. The first 3 letters from their last name and the first letter
from their first name.
Click to expand...

Karen Johnston
Kevin Johns
Kelly Johannson
Kanda Johannes

????

Even without the problem you raise... Names are NOT suitable primary keys!
This system has proven to be sufficiently robust to use this field as a
primary key in several tables
As it is a primary key there isn't any one place that is looked up.
Occasionally, someone gets married and changes their name.

Is their a way to cycle through all tables in the database, finding a value
and replacing it with another.
I know I can do it with one table with an update query, but what about
several tables at once.

I should add that the way this database has evolved its current design may
be far from ideal, buts thats the problem ... it evolved, rather than
instantly created from a good design.
Click to expand...

BACK UP YOUR DATABASE before doing this, just in case!!!

You can (to make the best of a bad situation) check that you have
Relationships defined from this employee table to all the related child tables
(and their children if you have more than one layer). Be sure that each
relationship is defined with Referential Integrity checked, and set Cascading
Updates on (by default it's off, and many would recommend that it be LEFT
off).

Then when you edit a record in the employee table, the changed value will
propagate to all the related tables.

You would really do well to use a unique EmployeeNumber instead of this
"intelligent key".
 
Thankyou everyone,

I've inherited this database from someone who had to make it in a hurry and
didn't know anything about relationships or normalisation.
As the need to change someones name is quite rare, I'll just go with an
update query on each table.
I was trying to build a "click one button" type of solution so that someone
else, who knows nothing about databases, could update it.
One day, when I get some time, I might redesign the whole thing.
Interestingly in our office of 140 people, the naming strategy works
everytime, but I appreciate that this is pure luck rather than good
management.

Thanks for all your advice.
 
Well, actually you could make it a click and perform on all tables. If you
set up an update query for each applicable table then from a form you could
present a list of existing persons, and a field to enter the new name. Could
check to see if the name already exists in the list, if it does then require
a different one. If it does not, then to run the update queries that you
created for each applicable table.

There's hard ways to go about it, but in VBA, you could basically go through
every table, if the field exists that you use for the "name" then update all
records that have the old name with the new name, if the field does not
exist, then get the next table. To best implement that, you would want to
make sure that every table that has this "name" in it has the field labeled
the same. That would simplify the search "procedure". This is also a bit of
a brute force method, rather than something well planned. :)
 
Back
Top