Autonumbers No Longer Unique

[Rob]

In a database converted from ACCESS 97 to ACCESS 2000, the
autonumber field in a table used to hold temporary data
before appending to a permanent (then deleted from the
temp) the autonumber field returns low (e.g. two digit)
values which the table has previously used dispite high (5
digit) numbers being present in the table, causing
duplication errors.

Has anyone any information on this seemingly fundimental
change in autonumber behaviour.

TIA

Rob

 

[Allen Browne]

There was a fault in msjet40.dll, which meant that it did silly things like
not resetting the AutoNumber after a compact, resetting it too low, or
setting it to largely negative values.

Microsoft says the fault is fixed in the Jet 4 SP7 fix, which you can
download from support.microsoft.com, under drivers, for Jet. To check if you
have SP7 installed, fined msjet40.dll (typically in windows\system32),
right-click and choose Properties, and check the File Version on the Version
tab. It should be 4.0.7328.

If you need to alter the Seed value of your AutoNumbers, this article
contains the code to do so:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

Note that if the AutoNumber is the primary key, it will not succeed in
creating a duplicate even when it tries to.

 

[TC]

I'm wondering whether to upgrade my main app (which is sold to people I
never meet) to check the DLL version at runtime, & warn the user if
required.

Yes? No?

TC

 

[TC]

Ok, thanks for the thoughts. I generally like to generalize things, so I'll
give some thought to having a table of DLL (or other component names) &
minimum version #s. Then, the app could check the version of the components
(if any) listed in that table. Then I could add or remove checks - even in
already-installed applications - by making updates to that table. This would
handle future similar situations, without the need to write new code.

Cheers,
TC

 

[Allen Browne]

We have not had much grief from this issue with installed apps.

Our most recent case was a client updating from Access 2 to 2000, and all
the counters were seeded to negative numbers after import. We wrote a quick
routine to reset the seeds, and released it here:
http://allenbrowne.com/ser-40.html

If you are updating your front-end often enough that you think it's worth
hard coding in the latest service pack for Jet, you could use the code Dev
Ashish provided here to determine the version number:
http://www.mvps.org/access/api/api0065.htm
Example:
? fGetProductVersion(Environ$("WinDir") & "\system32\msjet40.dll")
4.0.7328.0