Duplicate numbers in "Autonumber" field -- help!

[Tom_F]

To comp.databases.ms-access --

I just discovered, to my more than mild dismay, that some tables in my
Microsoft Access 2003 database have duplicate numbers in the
"AutoNumber" field. (Field Size is set to "Long Integer", and New
Values is set to "Increment".) I know that an old version of the Jet
database engine can cause this problem, but my version of msjet40.dll
is 4.0.8618.0, which is supposedly bug-free in this respect. I am
using Windows XP, SP2. (I started developing this database in 2006,
and might possibly have used an earlier version of Windows XP, and
Jet back then, however.) Doing "compact/repair" on my database does
not remove the duplicates.

I checked out Allen Browne's webpage on this issue:

http://allenbrowne.com/ser-40.html

but none of the "Causes" of this problem seem to fit my situation ...
as far as I can tell.

If I define a "unique" index on an AutoNumber field, this would at
least alert me of potential trouble in the future -- is this correct?
(My thinking is that Access would then prohibit me from entering a new
record if it "saw" a duplicate number in the AutoNumber field.)

Does anyone have any ideas on this? I am relying on uniqueness of my
AutoNumber field, to unambiguously identify records -- so this is an
important issue for me. Thanks very much,

--Tom
Tom Frenkel
email: frethoa AT aol DOT com

[Allen Browne]

A unique index is certainly the obvious solution, if you want the values to
be unique.

Importing data from an old database (such as Access 2) can set the Seed
wrongly. But the most common issue is the action query that appends records
and you end up with the seed set wrongly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom_F" <fr*****@aol.comwrote in message
news:11*********************@c51g2000cwc.googlegro ups.com...

To comp.databases.ms-access --

I just discovered, to my more than mild dismay, that some tables in my
Microsoft Access 2003 database have duplicate numbers in the
"AutoNumber" field. (Field Size is set to "Long Integer", and New
Values is set to "Increment".) I know that an old version of the Jet
database engine can cause this problem, but my version of msjet40.dll
is 4.0.8618.0, which is supposedly bug-free in this respect. I am
using Windows XP, SP2. (I started developing this database in 2006,
and might possibly have used an earlier version of Windows XP, and
Jet back then, however.) Doing "compact/repair" on my database does
not remove the duplicates.

I checked out Allen Browne's webpage on this issue:

http://allenbrowne.com/ser-40.html

but none of the "Causes" of this problem seem to fit my situation ...
as far as I can tell.

If I define a "unique" index on an AutoNumber field, this would at
least alert me of potential trouble in the future -- is this correct?
(My thinking is that Access would then prohibit me from entering a new
record if it "saw" a duplicate number in the AutoNumber field.)

Does anyone have any ideas on this? I am relying on uniqueness of my
AutoNumber field, to unambiguously identify records -- so this is an
important issue for me. Thanks very much,

--Tom
Tom Frenkel
email: frethoa AT aol DOT com

[Tom_F]

Mr. Browne --

thanks very much! But I am still rather stunned that Microsoft
nowhere seems to document that duplicate AutoNumbers seem to STILL be
a problem. In the Access help file for "AutoNumber", Microsoft
defines this Data Type as "A UNIQUE sequential (incremented by 1)
number or random number assigned by Microsoft Access whenever a new
record is added to a table." I think if MIcrosoft makes such a
statement, they have an obligation to live up to it. Or at the very
least, to explain why they are NOT living up to it! Regards,

--Tom

On Mar 12, 8:35 pm, "Allen Browne" <[e-mail]:

A unique index is certainly the obvious solution, if you want the values to
be unique.

Importing data from an old database (such as Access 2) can set the Seed
wrongly. But the most common issue is the action query that appends records
and you end up with the seed set wrongly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom_F" <[e-mail] in message

news:11*********************@c51g2000cwc.googlegro ups.com...

To comp.databases.ms-access --

I just discovered, to my more than mild dismay, that some tables in my
Microsoft Access 2003 database have duplicate numbers in the
"AutoNumber" field. (Field Size is set to "Long Integer", and New
Values is set to "Increment".) I know that an old version of the Jet
database engine can cause this problem, but my version of msjet40.dll
is 4.0.8618.0, which is supposedly bug-free in this respect. I am
using Windows XP, SP2. (I started developing this database in 2006,
and might possibly have used an earlier version of Windows XP, and
Jet back then, however.) Doing "compact/repair" on my database does
not remove the duplicates.

I checked out Allen Browne's webpage on this issue:

http://allenbrowne.com/ser-40.html

but none of the "Causes" of this problem seem to fit my situation ...
as far as I can tell.

If I define a "unique" index on an AutoNumber field, this would at
least alert me of potential trouble in the future -- is this correct?
(My thinking is that Access would then prohibit me from entering a new
record if it "saw" a duplicate number in the AutoNumber field.)

Does anyone have any ideas on this? I am relying on uniqueness of my
AutoNumber field, to unambiguously identify records -- so this is an
important issue for me. Thanks very much,

--Tom
Tom Frenkel
email: frethoa AT aol DOT com

[storrboy]

If you think that through, it's not really the Autonumber's fault.
The DB will not (as far as I know) generate the same number twice in
the same field. If you import records containing an autonumber, or
otherwise force the addition of a duplicate number, it's not the
generators fault. It thought the last number was 7, but you added
7,8,9,10 while it wasn't looking. With a unique index Jet prevents the
addition of duplicates, without it, it dosen't care - no matter how
that number came to be.

[Tom_F]

But that's just the point. As far as I know, I'm not "importing" or
"forcing" anything. In case it's helpful, below is a sample of the
VBA code I use to add a new record. If there already IS a record in
the database meeting the "stLinkCriteria", the form pulls in that
record. If there is NOT, the form displays a new record, blank except
for the fields which are set so that they meet the "stLinkCriteria".
USUALLY, this new record seems to have a new number in the
"AutoNumber" field. But apparently NOT ALWAYS. :( Is something
wrong with my coding strategy?
Thanks, Best,

--Tom

' ------------------- begin code ----------------------------
Private Sub CASA_Click()
On Error GoTo Err_casa_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCASA_main"

'Place info in global area so CASA_main form can access it for
"default value" of field
Forms!frmGLOBAL_VARS.namAssess = "Baseline"

' Need info below in case this record is already present
stLinkCriteria = "[IndivID]=" & "'" & Forms![frmSelectIndivID].
[IndivID] & "'" _
& "and [Assessmt_Adol] = ""Baseline"""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_casa_Click:
Exit Sub

Err_casa_Click:
MsgBox Err.Description
Resume Exit_casa_Click
' --------------- end code -----------------------------------

Hope this new information will be of use in diagnosing my situation!
Thanks, Best,

--Tom

On Mar 16, 4:41 pm, "storrboy" <[e-mail]:

If you think that through, it's not really the Autonumber's fault.
The DB will not (as far as I know) generate the same number twice in
the same field. If you import records containing an autonumber, or
otherwise force the addition of a duplicate number, it's not the
generators fault. It thought the last number was 7, but you added
7,8,9,10 while it wasn't looking. With a unique index Jet prevents the
addition of duplicates, without it, it dosen't care - no matter how
that number came to be.

[storrboy]

Other than a few things I'd do differently and a potentially missing
space in your stLinkCriteria (& "and ....) I don't see anything
inherently wrong. But when the form opens and no records match, what
causes it to move to a new record? When I tried something similar, the
form stated it was 'filtered' and the new record button was disabled.

[Allen Browne]

Tom, I don't think that code is the origin of the problem you are
experiencing. You are assigning a value to something (variaible? text box?)
on the frmGLOBAL_VARS, but that is clearly not an autonumber. You are using
a WhereCondition to OpenForm, but that's not assigning anything either. The
problem is elsewhere.

In reply to your earlier post, you will often find the documentation to be
lacking. Your brief quote
A UNIQUE sequential (incremented by 1)
number or random number ...
is wrong in at least 3 places:

a) Autonumbers should be unique, but you cannot guarantee that unless you
also add a unique index to the field. (Yes, it's a bug.)

b) AutoNumbers are not sequential. Skipped autonumbers (from aborted or
deleted records) mean the numbers are almost never sequential. (c) also
impacts this.

c) AutoNumbers are not necessarily incremented by 1. For 7 years now, it has
been possible to set any increment you wish (e.g. by 10s or 100s.)

So, I can understand your frustration. The documentation should be updated,
and the bug should be fixed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom_F" <fr*****@aol.comwrote in message
news:11********************@l77g2000hsb.googlegrou ps.com...

But that's just the point. As far as I know, I'm not "importing" or
"forcing" anything. In case it's helpful, below is a sample of the
VBA code I use to add a new record. If there already IS a record in
the database meeting the "stLinkCriteria", the form pulls in that
record. If there is NOT, the form displays a new record, blank except
for the fields which are set so that they meet the "stLinkCriteria".
USUALLY, this new record seems to have a new number in the
"AutoNumber" field. But apparently NOT ALWAYS. :( Is something
wrong with my coding strategy?
Thanks, Best,

--Tom

' ------------------- begin code ----------------------------
Private Sub CASA_Click()
On Error GoTo Err_casa_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCASA_main"

'Place info in global area so CASA_main form can access it for
"default value" of field
Forms!frmGLOBAL_VARS.namAssess = "Baseline"

' Need info below in case this record is already present
stLinkCriteria = "[IndivID]=" & "'" & Forms![frmSelectIndivID].
[IndivID] & "'" _
& "and [Assessmt_Adol] = ""Baseline"""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_casa_Click:
Exit Sub

Err_casa_Click:
MsgBox Err.Description
Resume Exit_casa_Click
' --------------- end code -----------------------------------

Hope this new information will be of use in diagnosing my situation!
Thanks, Best,

--Tom

On Mar 16, 4:41 pm, "storrboy" <[e-mail]:

>If you think that through, it's not really the Autonumber's fault.
The DB will not (as far as I know) generate the same number twice in
the same field. If you import records containing an autonumber, or
otherwise force the addition of a duplicate number, it's not the
generators fault. It thought the last number was 7, but you added
7,8,9,10 while it wasn't looking. With a unique index Jet prevents the
addition of duplicates, without it, it dosen't care - no matter how
that number came to be.

[Tom_F]

Storrboy --

On the "Properties" of my form, I have it set to "Allow additions:
Yes". Is your form set this way? I think a "Yes" here (or specifying
"AllowAdditions" in the "OpenForm" action), is necessary to get a new
record, if no matching ones are already present. Best,

--Tom

On Mar 16, 11:06 pm, "storrboy" <[e-mail]:

Other than a few things I'd do differently and a potentially missing
space in your stLinkCriteria (& "and ....) I don't see anything
inherently wrong. But when the form opens and no records match, what
causes it to move to a new record? When I tried something similar, the
form stated it was 'filtered' and the new record button was disabled.

[Tom_F]

Mr. Browne --

Thank you for vindicating my code. :) But I just have a question
about how a unique index would work in this situation. If the
AutoNumber field has a unique index, would that (a) "force" all
numbers that Access assigns to that field to be unique? OR (b) in
case Access did assign a non-unique number, would that make everything
stop in its tracks with an error message like: "Change in table would
create duplicate values in the index". Obviously, the latter
alternative would not create a very user-friendly situation! Thanks
much,

--Tom

On Mar 17, 12:32 am, "Allen Browne" <[e-mail]>
wrote:

Tom, I don't think that code is the origin of the problem you are
experiencing. You are assigning a value to something (variaible? text box?)
on the frmGLOBAL_VARS, but that is clearly not an autonumber. You are using
a WhereCondition to OpenForm, but that's not assigning anything either. The
problem is elsewhere.

In reply to your earlier post, you will often find the documentation to be
lacking. Your brief quote
A UNIQUE sequential (incremented by 1)
number or random number ...
is wrong in at least 3 places:

a) Autonumbers should be unique, but you cannot guarantee that unless you
also add a unique index to the field. (Yes, it's a bug.)

b) AutoNumbers are not sequential. Skipped autonumbers (from aborted or
deleted records) mean the numbers are almost never sequential. (c) also
impacts this.

c) AutoNumbers are not necessarily incremented by 1. For 7 years now, it has
been possible to set any increment you wish (e.g. by 10s or 100s.)

So, I can understand your frustration. The documentation should be updated,
and the bug should be fixed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom_F" <[e-mail] in message

news:11********************@l77g2000hsb.googlegrou ps.com...

But that's just the point. As far as I know, I'm not "importing" or
"forcing" anything. In case it's helpful, below is a sample of the
VBA code I use to add a new record. If there already IS a record in
the database meeting the "stLinkCriteria", the form pulls in that
record. If there is NOT, the form displays a new record, blank except
for the fields which are set so that they meet the "stLinkCriteria".
USUALLY, this new record seems to have a new number in the
"AutoNumber" field. But apparently NOT ALWAYS. :( Is something
wrong with my coding strategy?
Thanks, Best,

--Tom

' ------------------- begin code ----------------------------
Private Sub CASA_Click()
On Error GoTo Err_casa_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCASA_main"

'Place info in global area so CASA_main form can access it for
"default value" of field
Forms!frmGLOBAL_VARS.namAssess = "Baseline"

' Need info below in case this record is already present
stLinkCriteria = "[IndivID]=" & "'" & Forms![frmSelectIndivID].
[IndivID] & "'" _
& "and [Assessmt_Adol] = ""Baseline"""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_casa_Click:
Exit Sub

Err_casa_Click:
MsgBox Err.Description
Resume Exit_casa_Click
' --------------- end code -----------------------------------

Hope this new information will be of use in diagnosing my situation!
Thanks, Best,

--Tom

On Mar 16, 4:41 pm, "storrboy" <[e-mail]:

If you think that through, it's not really the Autonumber's fault.
The DB will not (as far as I know) generate the same number twice in
the same field. If you import records containing an autonumber, or
otherwise force the addition of a duplicate number, it's not the
generators fault. It thought the last number was 7, but you added
7,8,9,10 while it wasn't looking. With a unique index Jet prevents the
addition of duplicates, without it, it dosen't care - no matter how
that number came to be.

[Allen Browne]

The answer is (b): the append fails if the AutoNumber is a duplicate, and
you have a unique index on the field.

Having said that, I have never had an end user phone up with that situation.
If you read the page I suggested, and design your database to avoid the
problem situations, it won't happen.

If you want to cover your tail and provide a specific message for the user
anyway, how about:
Sorry: the database need compacting before you can save that.
The compact/repair process (on the back end, of course) resets the
autonumber seed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom_F" <fr*****@aol.comwrote in message
news:11**********************@l75g2000hse.googlegr oups.com...

Mr. Browne --

Thank you for vindicating my code. :) But I just have a question
about how a unique index would work in this situation. If the
AutoNumber field has a unique index, would that (a) "force" all
numbers that Access assigns to that field to be unique? OR (b) in
case Access did assign a non-unique number, would that make everything
stop in its tracks with an error message like: "Change in table would
create duplicate values in the index". Obviously, the latter
alternative would not create a very user-friendly situation! Thanks
much,

--Tom

On Mar 17, 12:32 am, "Allen Browne" <[e-mail]>
wrote:

>Tom, I don't think that code is the origin of the problem you are
experiencing. You are assigning a value to something (variaible? text
box?)
on the frmGLOBAL_VARS, but that is clearly not an autonumber. You are
using
a WhereCondition to OpenForm, but that's not assigning anything either.
The
problem is elsewhere.

In reply to your earlier post, you will often find the documentation to
be
lacking. Your brief quote
A UNIQUE sequential (incremented by 1)
number or random number ...
is wrong in at least 3 places:

a) Autonumbers should be unique, but you cannot guarantee that unless you
also add a unique index to the field. (Yes, it's a bug.)

b) AutoNumbers are not sequential. Skipped autonumbers (from aborted or
deleted records) mean the numbers are almost never sequential. (c) also
impacts this.

c) AutoNumbers are not necessarily incremented by 1. For 7 years now, it
has
been possible to set any increment you wish (e.g. by 10s or 100s.)

So, I can understand your frustration. The documentation should be
updated,
and the bug should be fixed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom_F" <[e-mail] in message

news:11********************@l77g2000hsb.googlegro ups.com...

But that's just the point. As far as I know, I'm not "importing" or
"forcing" anything. In case it's helpful, below is a sample of the
VBA code I use to add a new record. If there already IS a record in
the database meeting the "stLinkCriteria", the form pulls in that
record. If there is NOT, the form displays a new record, blank except
for the fields which are set so that they meet the "stLinkCriteria".
USUALLY, this new record seems to have a new number in the
"AutoNumber" field. But apparently NOT ALWAYS. :( Is something
wrong with my coding strategy?
Thanks, Best,

--Tom

' ------------------- begin code ----------------------------
Private Sub CASA_Click()
On Error GoTo Err_casa_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCASA_main"

'Place info in global area so CASA_main form can access it for
"default value" of field
Forms!frmGLOBAL_VARS.namAssess = "Baseline"

' Need info below in case this record is already present
stLinkCriteria = "[IndivID]=" & "'" & Forms![frmSelectIndivID].
[IndivID] & "'" _
& "and [Assessmt_Adol] = ""Baseline"""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_casa_Click:
Exit Sub

Err_casa_Click:
MsgBox Err.Description
Resume Exit_casa_Click
' --------------- end code -----------------------------------

Hope this new information will be of use in diagnosing my situation!
Thanks, Best,

--Tom

On Mar 16, 4:41 pm, "storrboy" <[e-mail]:
If you think that through, it's not really the Autonumber's fault.
The DB will not (as far as I know) generate the same number twice in
the same field. If you import records containing an autonumber, or
otherwise force the addition of a duplicate number, it's not the
generators fault. It thought the last number was 7, but you added
7,8,9,10 while it wasn't looking. With a unique index Jet prevents the
addition of duplicates, without it, it dosen't care - no matter how
that number came to be.