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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|