Re: updating all rows within same field that have same values from separate table
From: Marshall Barton (marshbarton_at_wowway.com)
Date: 02/13/04
- Next message: Neil: "Re: Change subform default view with VBA"
- Previous message: Glenn E. White: "Populated Form Datasheet from Table Field"
- In reply to: justin_at_STI: "updating all rows within same field that have same values from separate table"
- Next in thread: John Vinson: "Re: updating all rows within same field that have same values from separate table"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 13 Feb 2004 08:37:58 -0600
justin@STI <justinSTI.11i2af@nospam.MSAccessForum.com>
wrote:
>I have 2 tables and one form. the one table gets modified by the form
>and the other does not. both tables have a field for username. But the
>table that does not get modified ( Yet ) needs to update the username
>if it is changed in the form. So once the username in the form is
>changed and table 1 gets updated I need a way to also update table 2's
>username. BUT this second table does not have unique usernames. it is a
>log table that shows activity of when someone logs into a certain
>application that is put in manually yet I've been told to find a way to
>update all fields of the specific username in table2 if table1 is
>modified.
>
>I have not played with VB much in about 4 years so I haven't the
>foggiest how to attempt to update 2 tables with one value yet with 2nd
>table all the fields with the previous username have to be changed to
>the new username.
Your question reeks of an inadequate understanding of
relational database normal form basics. I'll try to point
you in the right directions, but you need to check out the
subject in Help or a book.
A fundamental concept of normalization is that you should
never duplicate data in a relational database. This means
you should adopt either one of two approaches to when
structuring your data into tables and their fields. One
approach is to use surrogate primary keys (usually a number)
so that you would never have those names in two tables.
Instead of your second table having a field with the name,
it would have a foreign key field containing the primary key
of the related record in the first table and thus never need
to be updated as your current structure requires.
The other approach is to use natural primary keys. In this
case, as long as you are using the name field as the first
table's primary key (a poor choice since lots of people can
have the same name), then the second table's foreign key
would actually contain the name. So you run into the point
of your question - how to keep them in sync when someone
changes their name. Fortunate;y, Access/Jet provide a
feature that makes sure you don't break the relationship
between the two tables. When you use the Relationships
window to specify the link between the primary key in the
first table and the foreign key in the second table, one of
the options provided by Enforce Referential Integrity is to
select Cascade Update so that Access/Jet will update the
foreign key field (i.e. the names) in the second table
automatically.
Some database systems do not provide a Cascade Update
feature, in which case it's up to the programmer to make
sure the data stays in sync, but bugs or a system crash can
pull the rug out from under the best of plans. The typical
technique is to execute an Update query to change the
foreign key of the appropriate records in the second table.
Using DAO, the code might look something like:
strSQL = "UPDATE table2 SET namefield =""" & newname _
& """ WHERE namefield =""" & oldname & """"
db.Execute strSQL, dbFailOnError
but you will have to determine when the name in the first
table is changed so you can be certain the update actually
happens. A major issue with doing it yourself, without the
benefits of referential integrity's Cascade Updates, is that
you can never allow a user see (or at least edit) the data
in either table using sheet view. You can only permit them
to see/edit the names using a form you've designed to trap
name changes so you can execute code like the above.
-- Marsh MVP [MS Access]
- Next message: Neil: "Re: Change subform default view with VBA"
- Previous message: Glenn E. White: "Populated Form Datasheet from Table Field"
- In reply to: justin_at_STI: "updating all rows within same field that have same values from separate table"
- Next in thread: John Vinson: "Re: updating all rows within same field that have same values from separate table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
