Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Aug 2010
    Location
    Camp Lejeune, NC
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Database primary key becoming obsolete due to Privacy issues

    Good morning, I have a database I created for work in 2002. We have over 32,000 records which the Primary Key was the sponsor SSN.

    Due to HIPAA regulations we are no longer going to use the SSN and have added a new field called DODID. I want this key to become the new primary key and no duplicates allowed.

    However all my queries and forms have been using the SSN as the primary and don't want to lose the data because we do still pull the information from 2002.

    Can I make the DODID a Primary key without corrupting the database.

    I am aware my forms and queries will have to be changed useing the DODID .


  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    New Lounger
    Join Date
    Aug 2010
    Location
    Camp Lejeune, NC
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Forgot to add in December 2013 we will no longer enter the SSN so I will have to change to field to a no "on needs to be filled in".

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,192
    Thanks
    201
    Thanked 781 Times in 715 Posts
    Hunter,

    What you need to do is plan very carefully!

    1. Backup the DB as it stands I'd actually make a couple on different media/locations.
    2. Make sure that you not only change the the Primary Key values but also you need to cascade the changed down into any tables using the Primary Key from the main table as a Foreign key for linking purposes be it the Primary Key of that table or just another field used to link back to the primary table.
    3. Craft your update queries carefully to take into effect the relationships made in step 2. I'd actually create a new table listing all the primary keys and then a field for the new DODID value. Then use this table in an update query with each of your tables link on the SSN and then Updating the DODID field with the value from the new table, change table repeat!
    4. Once all the tables have been updated you can then go into design mode and change the primary key field and delete the SSN field.
    5. Fix your queries/reports and Test, Test, Test!


    There may be an easier way if you fully understand how the cascading changes feature works in Access but I know the above method will work and it is easier to understand IMHO. Good Luck
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •