change primary key

  • Thread starter Thread starter Susan
  • Start date Start date
S

Susan

Seven yrs ago when I developed my system (now in Access
2000) I used Social Security Number as my primary key in
one of my tables. Now with so much security fraud, I want
to change it to an autonumber (which I already have).

The problem is when I go into the table in design view,
click on my ssn key field and click on the key icon, it
tells me to go to Relationship screen and delete the
links. When I try to do this it says I cannot. I even
deleted the table in Relationship screen and went back to
the table design to try and change it but got the same
message. WHAT am I missing here?

Thanks in advance. Susan
 
You can't really "delete" a table in Relationships view, you are simply
hiding it. Even while hidden it retains any relationships it had while
visible.

-Open the Relationships window
-Use "Show Table" to display the table you want to work with
-Right click on that table and select "Show Direct"
(this displays *all* tables that have a relationship with the selected
table, even if currently hidden)
-Right click on the relationships you want to delete and select Delete.
(Note: the actual tables involved in these relationships can not be open
elsewhere in the db during this step or it will fail)

Hope this helps,
 
You would need to remove the data from the table delete
the relationship and then change the Primary key...
 
Seven yrs ago when I developed my system (now in Access
2000) I used Social Security Number as my primary key in
one of my tables. Now with so much security fraud, I want
to change it to an autonumber (which I already have).

The problem is when I go into the table in design view,
click on my ssn key field and click on the key icon, it
tells me to go to Relationship screen and delete the
links. When I try to do this it says I cannot. I even
deleted the table in Relationship screen and went back to
the table design to try and change it but got the same
message. WHAT am I missing here?
Click to expand...

To delete the relationships in the relationship screen, you must
select the *JOIN LINE* between the tables - not the table icon itself.
Select the line (it will darken) and press the Delete key, and accept
Access' warning.

You'll want to (if you have not done so already) first add a Long
Integer field to each related table, run an Update query updating it
to the main table's autonumber value (linking by SSN), and (after
removing the SSN relationship) reestablishing the relationship from
the Autonumber to the foreign key. You can then delete the SSN field
from all the related tables.
 
You would need to remove the data from the table delete
the relationship and then change the Primary key...
Click to expand...

It's not actually necessary to remove any data to remove the
relationship.
 
Back
Top