A2002: Duplicate values in autonumber fields !!!

[Vladimír Cvajniga]

I'm experiencing duplicate values in autonumber fields (PrimaryKey = True,
Unique = True) in A2002 database. What's going on? How could it happen? To
me it seems as a SUPER BUG which should never leave Microsoft!!!
Grrrrrrrr........

Did MS guys & gals test A2002 before they released it?

:-( :-( :-(

Vlado

 

[Scott McDaniel]

I'm experiencing duplicate values in autonumber fields (PrimaryKey = True,
Unique = True) in A2002 database. What's going on? How could it happen? To
me it seems as a SUPER BUG which should never leave Microsoft!!!
Grrrrrrrr........

This is a confirmed problem if you're using Jet version 4.0.2927.4 and earlier. You need to update to the latest Jet
service pack:

http://support.microsoft.com/kb/291162/EN-US/

Did MS guys & gals test A2002 before they released it?

:-( :-( :-(

Vlado

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Vladimír Cvajniga]

TYVM for your response, Scott.

Same problems in A2003 and A2007, see
http://support.microsoft.com/kb/884185. Microsoft Jet 4.0 Database Engine
Service Pack 8 (SP8) may be one of the reasons that make duplicate
autonumbers appear. AFAIK, SP8 is the latest one...
My version of Msjet40.dll is 4.0.8618.0 (see MS Bulletin MS04-014) which is
higher then SP8 (4.0.8015.0). That's why I think I'm lost, ie. there's no
solution for the duplicate-autonumber-bug at the moment.

BTW, there are different versions of MS Jet 4 SP4 (for different operating
systems). Does A2002 PDW handle Msjet40.dll correctly so that end users
don't have to install latest SP if they have "my" Msjet40.dll or newer?

Vlado

 

[Vladimír Cvajniga]

P.S. There's wrong link to WindowsXP-KB829558-x86-CSY.exe (Czech version) at
http://support.microsoft.com/kb/239114/#. Instead, it points to
windowsxp-kb829558-x86-enu.exe (English version).
My lord... :-( :-( :-(

Vlado

 

[Scott McDaniel]

TYVM for your response, Scott.

Same problems in A2003 and A2007, see
http://support.microsoft.com/kb/884185. Microsoft Jet 4.0 Database Engine
Service Pack 8 (SP8) may be one of the reasons that make duplicate
autonumbers appear. AFAIK, SP8 is the latest one...
My version of Msjet40.dll is 4.0.8618.0 (see MS Bulletin MS04-014) which is
higher then SP8 (4.0.8015.0). That's why I think I'm lost, ie. there's no
solution for the duplicate-autonumber-bug at the moment.

If you're running the latest SP, then perhaps there are some problems with your database. Have you tried importing all
your objects to a new, blank database?

BTW, there are different versions of MS Jet 4 SP4 (for different operating
systems). Does A2002 PDW handle Msjet40.dll correctly so that end users
don't have to install latest SP if they have "my" Msjet40.dll or newer?

No. The PDW won't deal with ANY updates unless you include them with the deployment. Typically your installer would
check for the correct version of the file and then either continue or alert the user and (a) install the update, if it's
included with the deployment, (b) launch a browser window so that the user could select the correct update package or
(c) abort the installation and tell the user to update their machine. Of the 3, (a) is the most "clean" but it can
produce huge deployments. (b) is probably the easiest to deal with, since you don't know what OS and SP your enduser
will be running.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Douglas J. Steele]

Please note that this is Aar.on Kem.pff pretending to be a reliable poster
to these newsgroups.

 

[Tom Wickerath]

if your silly database can't do silly autonumbers reliably--- then you
should be using SQL Server and Access Data Projects

 

[Tom Wickerath]

stop blaming everything else but the database format

if you use a piece of shit database, you're going to have problems

move to SQL Server and they don't subject you to crap like this

 

[Vladimír Cvajniga]

Any ideas how to avoid duplicate autonumbers in existing database? I have
imported all objects into a new database and is seems it helped but I'm
affraid that duplicates may corrupt data integrity in existing end users'
databases. It's a FATAL BUG and I hope we'll have a solution really soon...

TIA

Vlado

P.S. Who's Aaron?

 

[Douglas J. Steele]

I wouldn't expect any changes for Access 2002: mainstream support for it
ended last year. http://support.microsoft.com/lifecycle/?p1=1266

Have you applied all the SPs that were released for it? (I believe this was
handled in one of them)

And Aaron's an annoying troll with an axe to grind. Just ignore him in all
his incarnations.

 

[Vladimír Cvajniga]

I think I have all service packs:
MS Office XP SP3
MS Office XP Developer 10SP1
Jet 4 SP 8

But Microsoft say that Jet 4 SP 8 may be the reason, see one of my previous
posts in this thread:
http://support.microsoft.com/kb/884185. Microsoft Jet 4.0 Database Engine
Service Pack 8 (SP8) may be one of the reasons that make duplicate
autonumbers appear. AFAIK, SP8 is the latest one...
My version of Msjet40.dll is 4.0.8618.0 (see MS Bulletin MS04-014) which is
higher then SP8 (4.0.8015.0). That's why I think I'm lost, ie. there's no
solution for the duplicate-autonumber-bug at the moment.

Vlado

 

[David W. Fenton]

I think I have all service packs:
MS Office XP SP3
MS Office XP Developer 10SP1
Jet 4 SP 8

But Microsoft say that Jet 4 SP 8 may be the reason, see one of my
previous posts in this thread:
http://support.microsoft.com/kb/884185. Microsoft Jet 4.0 Database
Engine Service Pack 8 (SP8) may be one of the reasons that make
duplicate autonumbers appear. AFAIK, SP8 is the latest one...
My version of Msjet40.dll is 4.0.8618.0 (see MS Bulletin MS04-014)
which is higher then SP8 (4.0.8015.0). That's why I think I'm
lost, ie. there's no solution for the duplicate-autonumber-bug at
the moment.

This problem happened in early versions of Jet 4, too. When it
occured, you needed to recreate the table and append the existing
data from it. This usually reset the counter.

Often, there was something wrong with the PK index, too.

I haven't seen the problem since Jet 4 SP6 was released, but the
article you cite seems to be a very specific situation.

 

[Vladimír Cvajniga]

Thx for your responce, David.

Long time ago I have created a conversion program for A97 databases. It's
very easy but powerful enough. It's core is something like that (I have
removed a few lines):

For Each tbl In dbOut.TableDefs
If tblExist(dbIn, tbl.Name) Then
Set rsIn = dbIn.OpenRecordset(tbl.Name)
Set rsOut = dbOut.OpenRecordset(tbl.Name)
While Not rsIn.EOF
rsOut.AddNew
For Each fld In .Fields
rsOut.Fields(fld.Name).Value = .Fields(fld.Name).Value
Next fld
rsOut.Update
.MoveNext
Wend
End If
Next tbl

I'd like to know if it's OK for autonumber fields. IMHO, this is the easiest
way how to convert old data to new template database with empty tables and
leave data integrity between "data" tables and "lookup" tables untouched.
Some say that writing values to autonumber fields may lead to mismatch in
autonumber seed. :-/
My conversion program has been working properly with A97 MDBs for several
years and I hope it should perform OK in A2002 as well. But I'm quite unsure
since yesterday when I experienced autonumber duplicates for the first
time... and, to be honest, I don't think that my problem was caused by my
conversion program.

Thank you very much for your time in advance.

Vlado

 

[David W. Fenton]

Long time ago I have created a conversion program for A97
databases. It's very easy but powerful enough. It's core is
something like that (I have removed a few lines):

For Each tbl In dbOut.TableDefs
If tblExist(dbIn, tbl.Name) Then
Set rsIn = dbIn.OpenRecordset(tbl.Name)
Set rsOut = dbOut.OpenRecordset(tbl.Name)
While Not rsIn.EOF
rsOut.AddNew
For Each fld In .Fields
rsOut.Fields(fld.Name).Value =
.Fields(fld.Name).Value
Next fld
rsOut.Update
.MoveNext
Wend
End If
Next tbl

I'd like to know if it's OK for autonumber fields. IMHO, this is
the easiest way how to convert old data to new template database
with empty tables and leave data integrity between "data" tables
and "lookup" tables untouched.

Well, if I were doing it, I'd just create a blank MDB in the new
version and import all the tables and relationships from the old
MDB, but use the IMPORT STRUCTURE ONLY option. Dunno why you need to
do it your way.

Some say that writing values to autonumber fields may lead to
mismatch in autonumber seed. :-/

It's not true. Appending a value does not touch the seed value or
even use it, because the default value is not used when you append a
value.

My conversion program has been working properly with A97 MDBs for
several years and I hope it should perform OK in A2002 as well.
But I'm quite unsure since yesterday when I experienced autonumber
duplicates for the first time... and, to be honest, I don't think
that my problem was caused by my conversion program.

No, it was not. It is a known issue caused by bugs in Jet 4. It
seems to me that exposing the seed value to programmatic
manipulation and allowing it to be changed without a compact (as was
implemented in Jet 4 for the first time) has made it less stable.

 

[aaron.kempf]

Douglas

im not annoying and im not a troll.


I'm just the only SQL Server developer that cares enough to help you
kids see the light.

How many times have you been brought into a company; and you're
introduced to some Access loser that is sitting in the corner, making
$12/hour and busting out forms and reports?

I feel bad for those kids; they should grow up and use SQL Server

 

[Paul Shapiro]

I have repeatedly seen the problem where appending a row with a specific
value for the autonumber resets the seed value to the next-higher integer.
Compact/Repair does not reset the seed value any more. My fix is to append a
new row with the correct seed value, and then delete that row. Now the seed
value is set to an unused value outside the current autonumber range.

 

[Allen Browne]

Paul, IIRC, the problem occurs when you execute an append query on an
*attached* table, specifying a value for the AutoNumber field.

And since the messed up table is in the back end, compacting the back end
will probably fix it (assuming an up to date copy of msjet40.dll.)

Here's a list of the issues I am aware of that cause this problem, and also
the code to programmatically reset the Seed:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have repeatedly seen the problem where appending a row with a specific
value for the autonumber resets the seed value to the next-higher integer.
Compact/Repair does not reset the seed value any more. My fix is to append
a new row with the correct seed value, and then delete that row. Now the
seed value is set to an unused value outside the current autonumber range.

 

[Paul Shapiro]

Allen, you're right that I've mostly seen it happen with linked tables when
inserting rows with specified values for the autonumber field. I've also
seen it when exporting SQL Server data to an mdb using SQL Server's DTS or
SSIS or when importing SQL Server data into an mdb using an Access append
query from linked SQL Server tables. But compacting the backend has not
fixed the problem in any case. Compacting used to fix the autonumber seeds,
but it hasn't for some time now, definitely not in Access 2003.

Thank you for the list of issues and the code to reset the seeds. I'll try
that next time I'm importing data.

Paul, IIRC, the problem occurs when you execute an append query on an
*attached* table, specifying a value for the AutoNumber field.

And since the messed up table is in the back end, compacting the back end
will probably fix it (assuming an up to date copy of msjet40.dll.)

Here's a list of the issues I am aware of that cause this problem, and
also the code to programmatically reset the Seed:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html