John Colby
jwcolby at ColbyConsulting.com
Tue Feb 7 22:22:18 CST 2006
You might want to copy the table out to an independent container, strip off
the additional fields, test to see if it still happens, and if so, send to
MS to analyze. Likely after moving out of the current BE it will work just
fine, but you never know.
John W. Colby
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jürgen Welz
Sent: Tuesday, February 07, 2006 10:45 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Duplicate AutoNumbers
Index Name: PrimaryKey
Field Name: EstimateID
Sort Order: Ascending
Properties:
Primary: Yes
Unique: Yes
Ignore Nulls: No
The field I mentioned named 'ID' is my replication synch field and is also
indexed unique, no duplicates and is equal to the AN plus an offset. A2k3
using A2k type BE. This database survived a compact & repair leaving dupes.
I had to reseed the AN at a higher number using the line of code I quoted
in an earlier post.
Ciao
J|rgen Welz
Edmonton, Alberta
jwelz at hotmail.com
>From: "John Colby" <jwcolby at colbyconsulting.com> Is it an autonumber or
>just an incrementing long? And if you look in the indexes, is there an
>index called PK and is that index Unique (yes, no duplicates)?
>
>Is this field plainly marked as the PK with the little key symbol? Or
>are you saying that a combination of two fields is the PK?
>
>John W. Colby
>www.ColbyConsulting.com
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JC<rgen Welz
>Sent: Tuesday, February 07, 2006 9:06 PM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Duplicate AutoNumbers
>
>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
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com