Duplicate Primary Key that can't be deleted

[paulml]

Hi Guys

I've searched the forum and can't find an answer to a tricky little situation that has arisen in our office.

Simply put, we have a table of "customers" who each have an autonumber Primary Key, (CustomerID). Every time a user places an order for a customer, this generates another Primary Key autonumber in a table called "Orders" called "OrdersID"

We have used this DB for almost ten years without any issues which I could not solve (with a little help from my friends on here) but now, somehow, we've ended up with TWO Primary Keys in the "Orders" which are the same number but have different Primary Keys from the "Customers" table. I can't see how this can happen as duplicates are not allowed in the OrdersID field.

Although I have deleted one of these orders, I cannot delete the other one. Any attempt to delete the other one returns: "The search key was not found in any record."

I realise that I've already deleted it when I deleted the other record but cannot find a way to get rid of this now defunct duplicate record.

If anyone can advise, I have two questions:
Firstly, what could cause this to happen?
Secondly, how do I get rid of the unwanted record?

Many, many thanks for your time, as always. I appreciate it!

:banghead:

 

[Alc]

Have you tried running a compact and repair on the database? I had a "phantom" record a little while back and just could not delete it until I did the C & R.

 

[paulml]

Have you tried running a compact and repair on the database? I had a "phantom" record a little while back and just could not delete it until I did the C & R.

That was going to be my next job. I just wondered if anyone had any clue as to why this might happen.

It's worked fine for many years and I'm puzzled as to how one of the users managed to produce this. I've often said that no matter how foolproof I make it, there's always a fool who will beat me!

Thank you for your advice.

 

[RuralGuy]

Here's a link you might find informative: http://allenbrowne.com/ser-40.html

 

[gemma-the-husky]

I just do not see how you can have a duplicated PK in any table.

PK is automatically, unique index, non-null

So, given the orders table. Do you mean duplicated autonumber? In that case is the autonumber the PK, or is it set as indexed, no duplicates, non null.

I suspect the table is not quite as you are describing.

 

[paulml]

I just do not see how you can have a duplicated PK in any table.

PK is automatically, unique index, non-null

So, given the orders table. Do you mean duplicated autonumber? In that case is the autonumber the PK, or is it set as indexed, no duplicates, non null.

I suspect the table is not quite as you are describing.

Thanks for your responses guys . .

In answer to Dave, Yes, it is an autonumber and yes it is the PK in this table. It is INDEXED: Yes (No Duplicates)

It's definitely 2 identical numbers in the same PK (Indexed, no duplicates) field. It's strange for sure.

I'll certainly try the C & R resolution early next week and report my findings.

Thanks everyone

 

[paulml]

I've run C & R on this and it's still proving impossible to delete this order. I still get the same "The search key was not found in any record." returned.

I've not tried deleting this from the table itself in the BE. I've only tried to delete through the user access form in the FE.

Do you guys think it's worth trying to locate this record in the actual table in the BE or will that make no difference?

Thanks again.

 

[RuralGuy]

You might try *importing* your BE into a fresh, new BE and see if the problem still exists. The import tends to straighten out a few things and leave any corruption behind.

 

[gemma-the-husky]

is it private, or can you upload the data for us to review. I struggle with the concept that you have a duplicate PK

PM me, if you don't want to publish it

 

[Cronk]

I had an occasion of a duplicate key (autonumber) field. It was on a network that was quite flaky for a while. I suspected what was being displayed was not what was stored, but then again who know what with a corrupted db.

Easiest is to restore from the last backup but...

First step is to import all objects into a new db. I wouldn't take the risk of only trying to treat the affected table.

If that does not work, repeat the import process into a new db but not including the crook table. Then create a blank table in the new db, and append all records from the corrupt db with key field values less than the corrupt record, and then all records above.

This is what I had to do. Lost one record which fortunately I could manually insert from an older backup.

 

[paulml]

Thank you to everyone for their help on this issue.
I've tried to PM some people but my post count won't allow it yet.
As I have many other tasks in the office, it may be a while before I can get my "MS Access" hat back on and so this problem is now on the back burner for a while.
I just wanted to take a moment to thank everyone.
As soon as I get some clear "quality" time in the office I'll pick it up again and get back on here.
Thanks again!

 

[The_Doc_Man]

The question is whether a query of "SELECT * FROM table WHERE pkfield = the offending value" will show one or two records. The last time I saw something like this, there was a conflict of some sort that had interrupted one of the record insertions so that it was incompletely updated. One of the records (in my case) was incompletely updated and could be deleted only by opening the database BE (Exclusive Open), opening the table in datasheet view, and removing the offending record by clicking on it and then deleting it manually.