473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Duplicate numbers in "Autonumber" field -- help!

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

Mar 12 '07 #1
9 11171
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
Mar 13 '07 #2
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" <AllenBro...@SeeSig.Invalidwrote:
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" <fret...@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

Mar 16 '07 #3
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.

Mar 16 '07 #4
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" <storr...@sympatico.cawrote:
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.

Mar 16 '07 #5


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.

Mar 17 '07 #6
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" <storr...@sympatico.cawrote:
>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.
Mar 17 '07 #7
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" <storr...@sympatico.cawrote:
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.

Mar 19 '07 #8
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" <AllenBro...@SeeSig.Invalid>
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" <fret...@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" <storr...@sympatico.cawrote:
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.

Mar 19 '07 #9
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" <AllenBro...@SeeSig.Invalid>
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" <fret...@aol.comwrote 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" <storr...@sympatico.cawrote:
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.
Mar 20 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: C L Humphreys | last post by:
Hi, I seem to have amassed a few thousand duplicates in an autonumber field. Is there a simple way of updating these to new numbers? Not sure if this is related - but I'm having problems...
6
by: Tom | last post by:
Is there a way to not display "Autonumber" in the primary key testbox when the PK is an autonumber in a new record? Tom
1
by: Knut B. | last post by:
I have a problem with a database of concerts, where I recently tried to change the existing database with some new rows/categories and some new names on a few of the existing ones. I have an id...
4
by: yf | last post by:
A KB article "http://support.microsoft.com/default.aspx?scid=kb;en-us;209599" tells that the maximum number of records that a table may hold if the PRIMARY key data type is set to AUTONUMBER is...
2
by: plawrow | last post by:
Hi, I am trying to assign "autonumber" for each line related to given record. TRANSACTION# DATE AMOUNT 3 01/01/05 100 3 01/01/05 150 3 ...
3
by: ben.werdmuller | last post by:
Hi, Is there an easy way in ASP/VBscript to grab an autonumber (primary key) field just after an SQL insert? This is probably easy, but I'm stuck .. Cheers.
1
by: S. van Beek | last post by:
Dear reader, By append a new record to a table I always expect the next higher value in the range of the autonumber field. But sometimes if I have deleted same records from a table the...
3
by: rdemyan | last post by:
I have a table that is imported from another system. I have no choice but to use the data as is; they will not change it. The records are not unique. For reasons I don't want to get into, I...
1
by: Paldrion | last post by:
I will need it to be in VBA, since I need to put it in an Access 2007 database and execute it with an Access macro. What I am doing is using a utility to find the hard drives' conditions on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.