Results 1 to 5 of 5
  1. #1
    AlPal is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    3

    Duplicated record including primary key

    Hi,

    Here's a problem which everyone will say can't happen because a primary key is unique by definition (and I agree). Yet happen, it has!



    I have a database that has been in use for six years now. It's a split design (each user has a local front end linked to the back end which is stored on a server). Just yesterday we noticed a record in one table has been duplicated in all respects, including its primary key. The original record is about three years old. Back up copies indicate that the duplication only occurred in the last three or four weeks. I've attached a snip of the table showing the duplication (record 649) and the design view showing the primary key field's properties.

    We've tried:
    1) running Compact and Repair to no avail; and
    2) deleting one of the records, but this was not possible because of links to it from other tables.

    As far as we can tell, there's nothing wrong with the code or db design and this is the first and last time it has happened. I can only guess that it's a result of a network glitch or simultaneous read/write, but this is just speculation.

    Does anyone have any clues? Bottom line, though, is that we just want to delete the one of the duplicates. The only way i can think of is to make a note of all new records and recent changes in all tables, restore the most recent valid backup then manually rewrite the new records and changes. Is there a simpler way?

    Thanks in advance

    Al
    Attached Thumbnails Attached Thumbnails Datasheet view.jpg   Design view.jpg  

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Read about it - never have seen it. Here's where: http://allenbrowne.com/ser-40.html
    If the info there won't help, why not just re-enter the single record as well as the child records, then delete the parent pair of records? What I don't get is why a 3 year old record would be replicated within the last 3 weeks. That suggests that there could be a design flaw that may rear it's ugly head again.

    There may also be a solution in removing the PK from the field, fixing the records, then re-establishing the PK. Whatever you do, play with copies!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    AlPal is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    3
    Thanks for that. We saw your link earlier, but it wasn't much help.

    We've been doing a bit more digging since my initial post. There's something funny going on with the PK. The snips above were actually taken from the FE on my computer rather than the BE. My colleague copied the BE to his desktop and opened it (directly, not using the FE) - the PK was in place and there was a significant number of links in the Relationships view. After closing and reopening, the number of relationships had reduced and the PK was lost. We were able to replicate this by copying again. Then we opened the server copy directly and the same thing happened. After this, we were able to delete one of the offending records and restore the PK. During this time, we noticed that one of the other tables had lost its PK, so restored it. Fortunately there had not been any duplication on that table.

    I now have to work through the relationships - so far so good.

    As for the 3yo record being affected: it it was updated about 3 weeks ago, so the duplication probably occurred then. Exactly how or why, if the PK was set, is a mystery.

    Al

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Curious to know what you saw when clicking on the + sign for 649, the exact same related records for each?
    I've also read about 'ghost' records in a sql server db as well as 'ghost' relationships in Access tables. The latter is what you seem to have experienced. Is it possible that the relationships were created in the FE (and maybe distributed this way) rather than in the BE, and subsequently the BE table design changed? That might explain the difference and the apparent update of the relationships. When the design changed, no one modified the relationships accordingly, hence when you opened the window again, Access reflected the design change by dumping what no longer fit. So what you were seeing in the FE was not what was really in the tables. Just my guess.

  5. #5
    AlPal is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    3
    When clicking the + sign for both, yes exactly the same related records were displayed for both.
    Interesting thoughts re the ghost relationships. It does sound about right. The curious thing is that in the BE relationships that I would expect to be there are not, yet it still works. The BE design hasn't changed in 6 years - any changes have all been done in the FE.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-24-2016, 08:01 AM
  2. Replies: 5
    Last Post: 11-30-2014, 08:12 PM
  3. Replies: 4
    Last Post: 04-01-2014, 02:33 PM
  4. Replies: 1
    Last Post: 04-10-2013, 11:50 AM
  5. Replies: 16
    Last Post: 02-06-2013, 03:06 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