A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Edit primary key values



 
 
Thread Tools Display Modes
  #1  
Old May 6th, 2010, 10:05 AM posted to microsoft.public.access.queries
GPE
external usenet poster
 
Posts: 5
Default Edit primary key values

Hi all,
We have a multi-user database where the primary key is set to a meaningful
text field. For many years, we have been happy with this and we never
expected that the primary key values would have to be changed. Now however,
there have been changes in gazetteer codes and as a consequence, the primary
key values in the main table and a few related tables in our database will
have to be changed. My question is: is it OK to change these values with a
series of update queries while keeping the primary key set on those table
columns? (To avoid problems, we will run the update queries when no one else
is logged onto the database, and after a backup of course.) Or should I
temporarily take out the primary key and set it again after the updating is
done? Has anyone else experience on this kind of tasks?
Thanks
  #2  
Old May 6th, 2010, 01:24 PM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default Edit primary key values

Simplest way to do this would be with cascading updates, so Access
automatically makes the change in the related tables if you change it in the
main table.

In the Relationships window, double-click the line joining the main table to
the related table.
Access pops up the Edit Relationships dialog.
Check the box for Cascading updates.
Repeat for the other relationships.

Now just change the text value in the main table, and Access does the rest
for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"GPE" wrote in message
...
Hi all,
We have a multi-user database where the primary key is set to a meaningful
text field. For many years, we have been happy with this and we never
expected that the primary key values would have to be changed. Now
however,
there have been changes in gazetteer codes and as a consequence, the
primary
key values in the main table and a few related tables in our database will
have to be changed. My question is: is it OK to change these values with a
series of update queries while keeping the primary key set on those table
columns? (To avoid problems, we will run the update queries when no one
else
is logged onto the database, and after a backup of course.) Or should I
temporarily take out the primary key and set it again after the updating
is
done? Has anyone else experience on this kind of tasks?
Thanks


  #3  
Old May 7th, 2010, 03:46 AM posted to microsoft.public.access.queries
GPE
external usenet poster
 
Posts: 5
Default Edit primary key values

The problem is not in creating the queries. I plan to create a form where
users can fill the new gazetteer code, and then run the update queries in the
background.
What I am wondering is whether the updating of primary key values will cause
corruption of the tables, as we will have to update thousands of values over
several tables. Will it cause Access to crash or is it OK to do update
queries on the primary key column?

"Allen Browne" wrote:

Simplest way to do this would be with cascading updates, so Access
automatically makes the change in the related tables if you change it in the
main table.

In the Relationships window, double-click the line joining the main table to
the related table.
Access pops up the Edit Relationships dialog.
Check the box for Cascading updates.
Repeat for the other relationships.

Now just change the text value in the main table, and Access does the rest
for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"GPE" wrote in message
...
Hi all,
We have a multi-user database where the primary key is set to a meaningful
text field. For many years, we have been happy with this and we never
expected that the primary key values would have to be changed. Now
however,
there have been changes in gazetteer codes and as a consequence, the
primary
key values in the main table and a few related tables in our database will
have to be changed. My question is: is it OK to change these values with a
series of update queries while keeping the primary key set on those table
columns? (To avoid problems, we will run the update queries when no one
else
is logged onto the database, and after a backup of course.) Or should I
temporarily take out the primary key and set it again after the updating
is
done? Has anyone else experience on this kind of tasks?
Thanks


.

  #4  
Old May 7th, 2010, 10:05 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default Edit primary key values

The important question is, "Have you created relationships between your
tables, with Referential Integrity enforced?"

Your answer is:

a) No: Then RUN and do it now. It will solve your problems.

b) Yes: Then forget the update queries, and use cascading updates as
explained earlier.

c) I don't know: Then here's an introduction:
http://allenbrowne.com/xbase-07.html

Any other approach is a waste of effort, and guarantees you will have bad
data at some point.

Using cascading updates will not corrupt your database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"GPE" wrote in message
...
The problem is not in creating the queries. I plan to create a form where
users can fill the new gazetteer code, and then run the update queries in
the
background.
What I am wondering is whether the updating of primary key values will
cause
corruption of the tables, as we will have to update thousands of values
over
several tables. Will it cause Access to crash or is it OK to do update
queries on the primary key column?

"Allen Browne" wrote:

Simplest way to do this would be with cascading updates, so Access
automatically makes the change in the related tables if you change it in
the
main table.

In the Relationships window, double-click the line joining the main table
to
the related table.
Access pops up the Edit Relationships dialog.
Check the box for Cascading updates.
Repeat for the other relationships.

Now just change the text value in the main table, and Access does the
rest
for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"GPE" wrote in message
...
Hi all,
We have a multi-user database where the primary key is set to a
meaningful
text field. For many years, we have been happy with this and we never
expected that the primary key values would have to be changed. Now
however,
there have been changes in gazetteer codes and as a consequence, the
primary
key values in the main table and a few related tables in our database
will
have to be changed. My question is: is it OK to change these values
with a
series of update queries while keeping the primary key set on those
table
columns? (To avoid problems, we will run the update queries when no one
else
is logged onto the database, and after a backup of course.) Or should I
temporarily take out the primary key and set it again after the
updating
is
done? Has anyone else experience on this kind of tasks?
Thanks


.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:22 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.