[AccessD] URGENT - Autonumber is CreatingDuplicate Numbers

Selina Iddon selina at easydatabases.com.au
Mon Oct 13 17:01:59 CDT 2003


Howdy
I have had this happen a couple of times.  If you make an append query
"insert into tbl_with_problem ([id]) select Max([tbl_with_problem].[id])+1"
this resets it.  Not a long term solution, but a quick one if it's urgent.

Cheers
Selina

----------------------------------------------------------------------------
---- Selina Iddon selina at easydatabases.com.au Ph: 0414 225 265 Easy Access
Databases
----- Original Message ----- 
From: "Charlotte Foust" <cfoust at infostatsystems.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Tuesday, October 14, 2003 7:24 AM
Subject: RE: [AccessD] URGENT - Autonumber is CreatingDuplicate Numbers


> Hen,
>
> The create new table approach always works.   If this bug works like the
> original, appending autonumbers is one of the problems--it doesn't in
> fact reset the seed properly.  One of the Jet 4 service packs (I think
> it was 4) broke it again after O2k SR-1 so that compacting did not reset
> the autonumber to the next highest number, although I believe SP7 has
> fixed that ... again.
>
> Charlotte Foust
>
> -----Original Message-----
> From: Henry Simpson [mailto:hsimpson88 at hotmail.com]
> Sent: Monday, October 13, 2003 12:32 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] URGENT - Autonumber is CreatingDuplicate Numbers
>
>
> You can use a database.execute "Insert Into..."  to set the autonumber
> field
> to the next valid autonumber or just open a table recordset and .addnew
> setting the autonumber field to the next desired number.  That should
> reset
> the autonumber again.  Failing that, I'd copy the table with data to a
> new
> table, rename the old table and the copy to the old name and change any
> required relationships as required.  When the new table is created, it
> should properly reset the autonumber again.
>
> Hen
>
>
> >From: "Terri Jarus" <jarus at amerinet-gpo.com>
> >Reply-To: Access Developers discussion and problem
> >solving<accessd at databaseadvisors.com>
> >To: <accessd at databaseadvisors.com>
> >Subject: Re: [AccessD] URGENT - Autonumber is CreatingDuplicate
> Numbers
> >Date: Mon, 13 Oct 2003 14:59:30 -0500
> >
> >So when I look at the Version, it is msjet40.dll  4.0.6218.0.  But when
>
> >I
> >go to your link to get SP7 - I have to run the
> >http://windowsupdate.microsoft.com which doesn't indicate that I need
> any
> >updates relative to Jet??
> >
> >I'll have the network dude check it out for me.  Thanks for your help.
> >
> >
> >Terri Jarus
> >Director, Contract Support Services
> >jarus at amerinet-gpo.com
> >314-542-1902
> >
> > >>> martyconnelly at shaw.ca 10/13/03 02:08PM >>>
> >You can check which version of jet that goes with msjet40.dll via MS
> >DLL hell database but it is not working at present. So just find dll in
>
> >windows explorer and right double click on it to look at version
> >properties and numbers
> >
> >http://support.microsoft.com/default.aspx?scid=/servicedesks/fileversio
> >n/dllinfo.asp&fp=1
> >
> >So just  get version SP7 below and update ;  SP4,5,6 all have glitches.
> >
> >http://support.microsoft.com/?kbid=282010
> >
> >Files in Jet 4.0 Service Pack 7
> >The current release, Jet 4.0 SP7, contains the following files:    File
> >Name           Version        Size
> >    -----------------------------------------
> >    Dao360.dll          3.60.6508.2    561,424
> >    Expsrv.dll          6.0.72.9589    380,957
> >    Msexch40.dll        4.0.6807.0     512,272
> >    Msexcl40.dll        4.0.6726.0     319,760
> >    Msjet40.dll         4.0.7328.0     1,507,600
> >    Msjetoledb40.dll    4.0.6807.0     348,432
> >    Msjint40.dll        4.0.6508.0     151,824
> >    Msjter40.dll        4.0.6508.0     53,520
> >    Msjtes40.dll        4.0.7328.0     241,936
> >    Msltus40.dll        4.0.6508.0     213,264
> >    Mspbde40.dll        4.0.6726.0     348,432
> >    Msrd2x40.dll        4.0.7328.0     422,160
> >    Msrd3x40.dll        4.0.6508.0     315,664
> >    Msrepl40.dll        4.0.6726.0     553,232
> >    Mstext40.dll        4.0.7328.0     258,320
> >    Mswdat10.dll        4.0.6508.0     831,760
> >    Mswstr10.dll        4.0.6508.0     614,672
> >    Msxbde40.dll        4.0.6726.0     348,432
> >    Vbajet32.dll        6.0.1.9431     30,749
> >
> >Some SP version numbers from some old code that I had to check version
> >of msjet40.dll directly You can have the code if you want to update it.
> >
> >Case 9 'Access 2000
> >         Select Case CStr(VerNum)
> >         Case Is = "4.0.4431.1"
> >             VerNum = VerNum & " (supplied with Access 2000 Jet SP5)"
> >         Case Is = "4.0.3714.7"
> >             VerNum = VerNum & " (supplied with Access 2000 Jet SP4)"
> >         Case Is = "4.00.2927.4"
> >             VerNum = VerNum & " (supplied with Access 2000 Jet SP3)"
> >         Case Else
> >             VerNum = VerNum & " (supplied with Access 2000, SP
> unknown)"
> >         End Select
> >     Case Else
> >         VerNum = VerNum & " (supplied with unknown Access version)"
> >     End Select
> >
> >NOTE: Microsoft Jet 4.0 Service Pack 4 (SP4) is included with Microsoft
>
> >Windows 2000 Service Pack 1 (SP1). Microsoft Jet 4.0 Service Pack 5
> >(SP5) is included with Microsoft Windows 2000 Service Pack 2 (SP2).
> >
> >Terri Jarus wrote:
> >
> > > I'm using Access 2002 SP-2.  How do I find out what Jet service pack
>
> > > I'm using?
> > >
> > > Also, I have A97 and, if you can believe it, Access2.0 installed on
> > > my computer in different directories.  My users are all on Access XP
>
> > > only.
> > >
> > > To fix the problem in the short term, I copied all my data into a
> > > new table allowing the new table to autogenerate the Autonumber
> > > field.  That seems to have alleviated the problem and allowed the
> > > users to put in new records.  I just don't want this to happen
> > > again.
> > >
> > > Terri Jarus
> > > Director, Contract Support Services
> > > jarus at amerinet-gpo.com <mailto:jarus at amerinet-gpo.com> 314-542-1902
> > >
> > > >>> cfoust at infostatsystems.com 10/13/03 10:37AM >>>
> > > There was a bug in Access 2000 that did not correctly set the seed
> > > for autonumbers when records were appended, including the autonumber
>
> > > value. That was cured in SR-1.  I've heard of this same problem
> > > recently in another forum but without details.  What version and
> > > service pack of Access are you using and what Jet service pack do
> > > you have installed?
> > >
> > > Charlotte Foust
> > >
> > > -----Original Message-----
> > > From: Terri Jarus [mailto:jarus at amerinet-gpo.com]
> > > <mailto:jarus at amerinet-gpo.com%5D>
> > > Sent: Monday, October 13, 2003 5:40 AM
> > > To: accessd at databaseadvisors.com
> > > Subject: [AccessD] URGENT - Autonumber is Creating Duplicate Numbers
> > >
> > >
> > > My Autonumber is creating duplicate number.  Of course, the records
> > > aren't being saved as the Autonumber needs to be unique.  What
> > > should I do and why is this happening?
> > >
> > > Thanks for any help you can give.
> > >
> > > Terri Jarus
> > > Director, Contract Support Services
> > > jarus at amerinet-gpo.com
> > > 314-542-1902
> > >
> > > --------------------------------------------------------------------
> > > ----
> > > ---
> > > This email and any files transmitted with it are confidential and
> > > intended solely for the use of the individuals or entities to whom
> they
> > > are addressed. If you have received this email in error please
> return
> > > it to the sender, and erase any copies thereof.
> > >
> > > Copyright 2003 AmeriNet 1nc.
> > >
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > > <http://www.databaseadvisors.com/>
> > >
> > >---------------------------------------------------------------------
> > >---
> > >
> > >_______________________________________________
> > >AccessD mailing list
> > >AccessD at databaseadvisors.com
> > >http://databaseadvisors.com/mailman/listinfo/accessd
> > >Website: http://www.databaseadvisors.com
> > >
> > >
> >
> >--
> >Marty Connelly
> >Victoria, B.C.
> >Canada
> >
> >
> >
> >_______________________________________________
> >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
>
> _________________________________________________________________
> Add photos to your messages with MSN 8. Get 2 months FREE*.
> http://join.msn.com/?page=features/featuredemail
>
> _______________________________________________
> 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
>
>




More information about the AccessD mailing list