[AccessD] Duplicate AutoNumbers

Jürgen Welz jwelz at hotmail.com
Tue Feb 7 20:05:59 CST 2006


John

No holes.  If you sort on the AN PK field, they sit there in nice neat pairs 
somewhere below the middle of the table.  I ran a:

CurrentDb.Execute ("Insert Into tblEstimate (ID, EstimateID) Values (16876, 
16876)")

from the immediate window to correctly set the AN and verified that new 
records are added with subsequent AN's.  I had two fields, both indexed 
unique for the purposes of a custom replication system that used a global 
offset number depending on the machine where the record was edited or added. 
  When records are added, the 2nd value is the PK plus an offset to 
determine which machine the record was added/edited from.  There are no 
duplications in the offset field, only the AN field.

What's really annoying is, a person will enter a new record (not in list) 
and when they attempt to look it up with the same combo, it displays the 
record from three years ago with the same AN PK.  The new record displays in 
the search combo and looks up the value in the hidden 1st column in the 
combo.  I use single record recordsets.  In the after update of the search 
combo, code sets the form recordsource to:

"Select * From tblEstimate Where EstimateID = " & Me.cboLookup

Both the old and new Names of the estimates appear in the search combo, but 
both display the first record matching the duplicated value in the combo.

Oh well.  I'll fix the data tonight once everyone gets out of the database.

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com



>From: "John Colby" <jwcolby at colbyconsulting.com>
>
>It can accept the "duplicate" if the original was deleted, i.e. if there is
>a "hole" in the sequence.
>
>
>John W. Colby
>www.ColbyConsulting.com
>
>
>-----Original Message-----
>
>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
>--
>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





More information about the AccessD mailing list