Autonumber field problem

[Diarmuid]

I have a Access 2000 multi user database, with the back end on a windows
2000 server. I have several tables with auto number primary keys. About once
a month two of the tables ( A + B) have a problem - the autonumber generated
is one already used. Thus the user gets an error message about duplication.
Repair and compact won't fix the problem - I have to run an append query to
set the next number to be generated.
All the latest patches and jet updates are installed on the server. Table A
is simple, just 4 fields with only a couple of hundred records. Normally
only changed by one user. It is never opened in recordsets, only changed in
a suboform.
Table B is more complex, with around 20,000 records and may be changed by
around 5 users at once. It is opened in code as a DOA recordset. Table B has
this problem a lot more often than table A.

Any suggestions?
Thanks
Diarmuid

 

[tina]

in TableB, is your autonumber field set to New Values: Increment.
if so, you might try changing it to New Values: Random.

 

[John Spencer (MVP)]

Have you installed all the patches and updates on the clients? You said you did
on the server, but didn't mention the clients. I really don't see how they
could cause this specific problem, but ...

This is a bug in Access 2000, but the latest updates should have fixed the problem.

 

[Michael, Cheng [MSFT]]

Hi Diarmuid,

From your descriptions, I understand you would like to have correct
autonumber field growth when there is multi users on this files.

First of all, What's the detail error message the customer get?
Like "Could not update. Currently locked by user 'Admin' on machine
'machinename'"
or "Couldn't update. Currently locked." ?

Secondly, If you always dirty a new record in the first instance and add a
record in the second instance while the first is still dirty and commit the
record in the first after adding the record in the second and then dirty
the next new record in the first before adding a record in the second. On
Windows 2000, you can do this about three times. On the 4th attempt you
will get an error saying the record is locked. It appears to hang, but it
is just waiting for the lock to release. If so, I think you should try to
elimiate dirty read by users.


The following KB will show you making 'autonumber' yourself with VBA and in
this way you could make full consideration for your application
ACC2000: How to Create a Multiuser Custom Counter
http://support.microsoft.com/?id=210194

Last but not the least, would you please check the latest updates of your
server? They should be:
Windows 2000 Service Pack 4 Network Install for IT Professionals
http://www.microsoft.com/downloads/details.aspx?FamilyID=1001aaf1-749f-49f4-
8010-297bd6ca33a0&DisplayLang=en

Office 2000 Update: Service Pack 3 (SP3)
http://www.microsoft.com/downloads/details.aspx?FamilyID=5c011c70-47d0-4306-
9fa4-8e92d36332fe&DisplayLang=en

Jet 4.0 Service Pack 8 (SP8) for Windows 2000 (KB829558)
http://www.microsoft.com/downloads/details.aspx?FamilyID=1e268b39-533e-48b0-
b8d7-1781befde1f8&DisplayLang=en

Hope this helps and if you have any questions or concerns, don't hesitate
to let me know.

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.