Autonumber duplication

[flogyu]

Hello,
have anyone ever met autonumber field duplication?
Phenomenon: an app. running genarally error-free, sometimes collapses due to
value duplication in autonumber field because seed value fails(decreasing).
As for I am the author, for sure, there are no any VBA code dealing with seed
value adjusting( at least Intentionally). Might it be an error in Jet engine,
or a tricky bug in my program, that leads to such an error like this?
Environment: Acc 2002, Jet 4.0.
The app runs over WindowsServer 2003, Terminal services.
Your help would be appreciated.
Flogyu

 

[Allen Browne]

There are several known bugs that cause JET 4 databases (Access 2000, 2002,
and 2003) to generate duplicate autonumbers.

A bad one was fixed in SP7 for JET 4, so you might like to check that you
have SP8. Info:
http://support.microsoft.com/kb/239114

Another issue is discussed in:
http://support.microsoft.com/?id=884185
but the problem is more generic than the kb recognises. Essentially,
executing any append query that specifies the value for the autonumber and
appends data to a linked table is likely to cause the problem.

Another issue includes a failed write that typically occurs if you have a
main form and a subform both bound to the same table.

Importing/converting from an older Access database can also set the Seed
badly.

More info and workarounds in this article:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

 

[flogyu]

Dear Rob, thank you for immed answering.
I do not understand clearly what you mean. How could an Access app restart
due to a failure w/o any err msg unless terminlal server?
Flogyu

 

[Van T. Dinh]

Try the Microsoft Knowledge Base articles:

http://support.microsoft.com/kb/209710

and

http://support.microsoft.com/kb/257408

for possible causes and resolutions.

 

[flogyu]

I have understood now what you ment. The app is huge, compared to usual Acc
apps. The trouble is that no any error report(run time error or sg like
this). I learned duplication from users laments telling some data items
disappeared. Traceing reason, I find duplicates in tables regarding
autonumber. I wrote codes to discover and recover them, restoring seed too. I
have recently put the appl. to wserver 2003 using its terminal services.
Before switching to w2003 Ts, this kind of error has not occured. An error
may occur w/o reporting, if somewhere in code i used (wrong way) error
handling. Now I start to check it, but I am scared of terminal server's
suspected influence.
Flogyu

 

[CathyJ]

Thank you Thank you Thank you -

2 days of trying to debug and your code fixed the problem (whatever it was -
it didn't seem to fit into any of your categories)