Jürgen Welz
jwelz at hotmail.com
Tue Feb 7 21:45:06 CST 2006
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 Jü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