[AccessD] Duplicate AutoNumbers

I had something similar a couple of weeks ago, new records were being
assigned AutoNumber values that already existed. However the unique  index
of the field still kicked in and threw a duplicate value error, so I was
able to spot exactly when it happened and fix it. The cure was to get
everyone out of the front-end and Compact/Repair the back-end. 

Now how your app is managing to accept the duplicate values is another
question. Sounds like the index is so corrupt that it's lost it's Unique
setting too. I'd try the compacting method first. Then open the table
directly in the back end and add a new record, just to see what the
AutoNumber value looks like.

Oh and William regarding your comment " which is one reason why you can't
trust them where audit sequences are required"... 

Tut Tut!!! Autonumbers are not supposed to be used for audit sequences, that
would be assigning a meaning to them instead of just using them to identify
a row in the table, yada, yada, yada. <vbg>

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
Sent: Tuesday, February 07, 2006 5:17 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Duplicate AutoNumbers


...huh? ...how are you getting DUPLICATE autonumber pk's ...does the field 
not require a unique an? ...I know that a corrupt mdb can restart an's on 
unused numbers which is one reason why you can't trust them where audit 
sequences are required ...but duplicates are new ...and damn scary :(

William

----- Original Message ----- 
From: "J??rgen Welz" <jwelz at hotmail.com>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, February 07, 2006 4:54 PM
Subject: [AccessD] Duplicate AutoNumbers


> Trouble call from users:
>
> Enter a name in search combo and a record from 3 years ago with a
> different
> name shows up.
> Have a look at the table.  I have 30 records duplicating the Autonumber 
> PK.
> She's gone back a bit over 3 years.  AN was at 17,000 and is now 
> duplicating
> rows from 7474 after a corrupt record with AN 2759985.  Corrupt record has
> text with characters I've never seen before and several foreign keys 
> showing
> 7474 and one showing 1677749760 (normal range of values from 0 through 3).
>
> Several of these duplicate records are subsequent to our last 
> compact/repair.  Now I must fix the records and divide the child 
> records between the proper parents.  Good thing there are lots of 
> backups so I can match the related records to the original parents.  
> What a waste of time.
>
> Ciao
> Jürgen Welz
> Edmonton, Alberta
> jwelz at hotmail.com
>
>
>


----------------------------------------------------------------------------
----


> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com