Results 1 to 3 of 3
  1. #1
    AlexSalvadori is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14

    Sharepoint Wizard resets Primary Keys but not related Records

    Hello all,



    We are trying to migrate an Access DB onto a new Sharepoint server. I used the Export Wizard to convert the existing tables into SP Lists, and it has been mostly succesful, but there's one significant problem:

    Sharepoint appears to reset the Primary Keys in all tables, so where - for instance - our Personnel table used to start at ID 3001 (because we had some test data which we subsequently deleted), it now starts at 1. This would be fine except it HAS NOT updated the related data in other tables, so a record relating to Person with ID 3001 should now relate to ID 1, but it doesn't. it relates to the NEW 3001 which is a completely unrelated Person. In essence, subordinate tables are now 'misaligned'.

    I'm going to have to start again from backup. What can I do to ensure the Wizard changes related ID's as well as Primary Keys?
    (Or easier still, can I tell Sharepoint not to change the PK's at all?)

    Thanks for your help,
    AS

  2. #2
    AlexSalvadori is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    14
    Update:

    As far as I can tell there's no way to tell Access/Sharepoint to leave the Primary Keys alone, I can only assume whoever programmed this had no idea what they were doing or why Primary keys are important, and should not be reduced to 1,2,3 just to sate your OCD

    Anyway With some patience I was able to restore the relationships manually - by identifying and comparing the records' newIDs to their oldIDs, then manually updating all related tables (I used Excel, but an Update Query would have worked too).

    This can also be avoided by resetting all Primary Keys yourself, before using the upload Wizard. Always take a backup, people

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Appreciate you returning and posting your solution. I for one have very little experience with integrating Access with sp so while I wanted to post something I figured it would not be helpful. In recent past I've looked into using Access with sp and it seems that M$ no longer thinks it's the cat's meow, so to speak. Just another flavour of the month, like deprecating DAO and web based databases.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use of primary keys
    By Homegrownandy in forum Access
    Replies: 6
    Last Post: 06-29-2015, 01:17 AM
  2. Replies: 12
    Last Post: 06-10-2014, 01:15 PM
  3. Replies: 3
    Last Post: 03-22-2014, 04:32 PM
  4. How to create two primary keys
    By Shabana123 in forum Database Design
    Replies: 1
    Last Post: 09-08-2012, 05:55 AM
  5. Replies: 1
    Last Post: 08-03-2010, 01:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums