Change primary key

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

For the last 5 yrs I have been using the ssn as primary
key for a table (using Access2000,split database,12
tables). Because of identity theft, the client wants to
have the ssn removed from the screens/tables. Is there a
way that I can change the ssn primary key field to an
AutoNum field -- for the existing customers and the new
ones to be added? Thank you.
 
sam said:
For the last 5 yrs I have been using the ssn as primary
key for a table (using Access2000,split database,12
tables). Because of identity theft, the client wants to
have the ssn removed from the screens/tables. Is there a
way that I can change the ssn primary key field to an
AutoNum field -- for the existing customers and the new
ones to be added? Thank you.
Click to expand...

Yes, and your client is doing the right thing. Here's how to do it.

ON A COPY:

Tables first:
1. Add an autonumber column to the table which uses the SSN as its Primary
Key.

2. Add the same column as a Number/Long Integer to every other table that
links to you main table with the SSN.

3. Use an update query to update the empty Long integer columns in all the
foreign key tables. Some sample SQL would be like this:

UPDATE Table1 INNER JOIN Table2 ON Table1.SSN = Table2.SSN SET Table2.NewID
= [Table_1].[NewID];

Then the rest:
4. Go through your forms, reports, and queries and replace SSN with NewID.
Using a tool like Speed Ferret or Find & Replace

5. Go through the code modules and use either one of the above utilities or
the built in Replace functionality (Edit ... Replace in the code window).

http://www.moshannon.com/

http://www.rickworld.com/products.html

Test your forms and reports. Your done. Depending upon how complex your app,
it should take from 1 to 3 hours to do it all.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks. I examined my table and found that I have an
autonum field in my main table - so I can use that. I
know how to add that to the other tables and how to link
them with the new primary key. I read up on Update
Queries but unfortunately I do not know SQL - can I do
your number 3 without knowing SQL?? With number 4 would I
just go into design view and use a find/replace that way?
As for number 5, I do not know how to access the code
modules. I have developed a terrific system which has
worked and grown well over the last 6 yrs but have never
learned SQL or the debugging tools. Hope it is possible
to do this without that knowledge! Thanks for any further
help.
-----Original Message-----
For the last 5 yrs I have been using the ssn as primary
key for a table (using Access2000,split database,12
tables). Because of identity theft, the client wants to
have the ssn removed from the screens/tables. Is there a
way that I can change the ssn primary key field to an
AutoNum field -- for the existing customers and the new
ones to be added? Thank you.
Click to expand...

Yes, and your client is doing the right thing. Here's how to do it.

ON A COPY:

Tables first:
1. Add an autonumber column to the table which uses the SSN as its Primary
Key.

2. Add the same column as a Number/Long Integer to every other table that
links to you main table with the SSN.

3. Use an update query to update the empty Long integer columns in all the
foreign key tables. Some sample SQL would be like this:

UPDATE Table1 INNER JOIN Table2 ON Table1.SSN = Table2.SSN SET Table2.NewID
= [Table_1].[NewID];

Then the rest:
4. Go through your forms, reports, and queries and replace SSN with NewID.
Using a tool like Speed Ferret or Find & Replace

5. Go through the code modules and use either one of the above utilities or
the built in Replace functionality (Edit ... Replace in the code window).

http://www.moshannon.com/

http://www.rickworld.com/products.html

Test your forms and reports. Your done. Depending upon how complex your app,
it should take from 1 to 3 hours to do it all.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.548 / Virus Database: 341 - Release Date: 12/5/2003


.
Click to expand...
 
Back
Top