Autonumber conflict

[Renee Kraft]

I've never seen something like this before:

Simple table(s) with PK / autonumber. That's how I set it up in the
beginning. The table now has 147 records with autonumbers between 1 and 190.
When I try to insert a new record Access attempts to assign an ID like 96
which already exists so the insert fails. If I try it again Access assumes
ID 97... fails again. Eventually I will get past ID 190...

Database and versions: Access 2002 SP 2. It's a "clean" back-end, has never
had any forms or modules in it. One main table with six related tables; two
of the six are giving me this problem. I fixed it in one table: first I
deleted and then pasted again all records with numbers higher than the
autonumber that Access remembered. But that's not a solution because this
application will have a lot of delete / insert / replace features therefore
I need to know what causes it and how to deal with it.
Jet 4 version with SP 7. Repaired / compacted the database several times -
same problem. Copied the database - same problem in the copy.

I hope somebody can shed some light on this. Thank you.
Renee
--
=======================
Renee Kraft
E-mail: (e-mail address removed)
--Remove ns from address--
=======================

 

[Allen Browne]

Renee, this is a bug in Access.
Jet 4 SP7 is supposed to fix it.
SP8 is just out: you could try downloading that from
support.microsoft.com

Run a search on your hard disk, and ensure you only have one copy of
msjet40.dll (other than in a service packs folder). Right-click it and check
the version number. It should be 4.0.8015.0 once you have Jet4 SP8
installed.

As a temporary workaround, the code in this link will examine all your
tables and reset the autonumber seed where necessary:
http://allenbrowne.com/ser-40.html

 

[Renee Kraft]

Thank you for the "workaround" Allen; it worked like a charm!

Seems that Jet 4 SP7 doesn't fix it. It was one of the first things that I
checked, and I only have one msjet40.dll. As far as I remember I created the
problem db before installing Jet 4 SP7 (in August) - I wonder if that has
something to do with it.

I still have to test it but even if it works in my environment it'll be
tough to make sure that all my clients' machines have the correct Jet
version. (We will have separate copies in remote locations.) SP 7/8 comes
with separate versions for each OS (!) - it's not just a matter of simply
adding a file to my install program.

I hope that somebody at MS reads this message.

Renee

 

[Allen Browne]

Renee, if you need to you can code your app. to read the Jet SP on start up.

Dev Ashish provides the API call here:
http://www.mvps.org/access/api/api0065.htm

 

[Renee Kraft]

Thanks Allen,
I also found an article on Tony's page about this: Verify appropriate Jet SP
is installed: http://www.granite.ab.ca/access/verifyjetsp.htm
Btw, I like your Access tips page and added it to my favorites!
Renee