Multi user and auto increment

[john]

Is it true that if I split my access database in backend and frontend and I
implement custom auto increment for the ID fields, that my database is ready
to be used in a multi-user environment? I found a zillion messages about
auto increment and read some of them but it's confusing. Can someone point
me to a simple custom auto increment example that I can download?
Thank you,
john

[Rick Brandt]

john wrote:

Is it true that if I split my access database in backend and frontend
and I implement custom auto increment for the ID fields, that my
database is ready to be used in a multi-user environment? I found a
zillion messages about auto increment and read some of them but it's
confusing. Can someone point me to a simple custom auto increment
example that I can download? Thank you,
john

The built in AutoNumber is the most robust "auto-applied" value in a
multi-user environment. Custom-coded auto-increment solutions can work well
in multi-user environments, but only if you do it right. The two methods
that work reliably are...

********************
Store the "NextID" auto-increment value in a table. When a new record is
saved you use code to...
Grab the value from the NextID table while applying an exclusive lock on
it
Apply the value to your record and save it
Increment the value in the NextID table and save, then release the table
lock.

********************
Use DMax() + 1 in the BeforeUpdate event of the form doing insertions.
BeforeUpdate is the ONLY event that ends with the record being committed to
disk so it has the smallest window of time where two users might possibly
calculate the same NextID value. The only caveat with BeforeUpdate is that
it can fire multiple times over the life of a record so your code needs to
add a test for either NewRecord or a Null ID.

BeforeInsert has the most appropriate sounding name for doing this, but
there is an indefinite period of time between that event firing and the
record being saved. During that interval all other users will grab that
same value and you will have conflicts.

The DefaultValue property has the same issue. The value would be calculated
as soon as the New Record position of the form is painted on the screen, but
any length of time could pass before a record is actually entered and saved.
Additionally, setting the Default Value property with a DMax() + 1
expression doesn't work at all in a continuous form (even with only one
user).

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[john]

Thanks for the info, which will save me a lot of time.
john

"Rick Brandt" <ri*********@hotmail.comschreef in bericht
news:mM*****************@newssvr12.news.prodigy.co m...

john wrote:

>Is it true that if I split my access database in backend and frontend
and I implement custom auto increment for the ID fields, that my
database is ready to be used in a multi-user environment? I found a
zillion messages about auto increment and read some of them but it's
confusing. Can someone point me to a simple custom auto increment
example that I can download? Thank you,
john

The built in AutoNumber is the most robust "auto-applied" value in a
multi-user environment. Custom-coded auto-increment solutions can work
well in multi-user environments, but only if you do it right. The two
methods that work reliably are...

********************
Store the "NextID" auto-increment value in a table. When a new record is
saved you use code to...
Grab the value from the NextID table while applying an exclusive lock
on it
Apply the value to your record and save it
Increment the value in the NextID table and save, then release the
table lock.

********************
Use DMax() + 1 in the BeforeUpdate event of the form doing insertions.
BeforeUpdate is the ONLY event that ends with the record being committed
to disk so it has the smallest window of time where two users might
possibly calculate the same NextID value. The only caveat with
BeforeUpdate is that it can fire multiple times over the life of a record
so your code needs to add a test for either NewRecord or a Null ID.

BeforeInsert has the most appropriate sounding name for doing this, but
there is an indefinite period of time between that event firing and the
record being saved. During that interval all other users will grab that
same value and you will have conflicts.

The DefaultValue property has the same issue. The value would be
calculated as soon as the New Record position of the form is painted on
the screen, but any length of time could pass before a record is actually
entered and saved. Additionally, setting the Default Value property with a
DMax() + 1 expression doesn't work at all in a continuous form (even with
only one user).

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com