Difficulty with multiple users adding records

[anetcorey]

I have difficulty when multiple users create records in a customer
service db in Access2003. If two new records are opened at the same
time one user will not be able to save record because both new records
initially were issued same id - the first user who saves his/her
record wins - the other user cannot save data - that user receives a
data violation message.

I understand why this is happening - but I am not sure of the cure. I
am a long time access db user and developer for a small group of
users. I can program vba at an "advanced beginner" level.

This problem has been haunting me for a while - maybe I've hit a
learning curve ceiling.

If anyone out there has some tips I sure would appreciate your input.

Thank you

 

[Bob Quintal]

(e-mail address removed) wrote in
:

I have difficulty when multiple users create records in a customer
service db in Access2003. If two new records are opened at the
same time one user will not be able to save record because both
new records initially were issued same id - the first user who
saves his/her record wins - the other user cannot save data - that
user receives a data violation message.

I understand why this is happening - but I am not sure of the
cure. I am a long time access db user and developer for a small
group of users. I can program vba at an "advanced beginner"
level.

This problem has been haunting me for a while - maybe I've hit a
learning curve ceiling.

If anyone out there has some tips I sure would appreciate your
input.

Thank you

How are you assigning the ID?

 

[John W. Vinson]

I have difficulty when multiple users create records in a customer
service db in Access2003. If two new records are opened at the same
time one user will not be able to save record because both new records
initially were issued same id - the first user who saves his/her
record wins - the other user cannot save data - that user receives a
data violation message.

I understand why this is happening - but I am not sure of the cure. I
am a long time access db user and developer for a small group of
users. I can program vba at an "advanced beginner" level.

This problem has been haunting me for a while - maybe I've hit a
learning curve ceiling.

If anyone out there has some tips I sure would appreciate your input.

Thank you

I use a (copyrighted, but variants are widely available on the web) technique
from Getz et al.'s _Access 97 Developer's Handbook_. It uses a table of
"seeds", one for each table which needs a unique ID. The code opens this table
exclusively; retrives the current ID for this table; increments it by one and
stores it back in the table; then closes the table and returns the value that
it retrived to the calling query.

Note that if you use an Autonumber Access takes care of this for you
automatically; an autonumber value is "used up" the instant the new record is
created, and another user will only get a duplicate if you've got a buggy
version of Access (and such versions have existed at times over the years).
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com