Yet Another Autonumber/Incremental Key Question

[Joe Williams]

Ok, from the posts I have read I understand that I want to avoid using the
autonumber field for anything meaningful in access. So, after reviewing the
posts I have seen two distinct ways to accomplish a meaningful incremental
counter:

1) Create a field that represents a meningful number. On new records, do a
dmax() function, add 1 to it, and use that value as the value for the
incremental field.

2) Create the incremental field and also a seperate table that stores just
one value, which represents the last number used. On new records, look up
this new number, add one, then update the lookup table and use this new
value as the value for your incremental field.

So, here are my questions regarding this two methods:

1) Which method is better in a multi-user environment? I might have 4 or 5
people entering records at once and I don't want thier to be any issues with
record locking or getting the same number.

2) If I am using this incremental field, is there still a need for an
autonumber field? What are the advantages/disadvantages to keeping the
autonumber field in addition to the new incremental field?


Thanks

Joe

 

[Allen Browne]

The point of method 2 is that you LOCK the table containing the single
record while you increment it and write that number to the main table, and
then unlock it again. This locking guarantees that no two people can be
given the same number at the same time. Naturally, you have to code in a
fixed number of retries with random delays so as to handle the locking
conflicts.

Method 1 is not as safe. Your best shot is to use the last possible moment
to perform the DMax(), i.e. do it in Form_BeforeUpdate (the last event
immediately before the record is saved).

If you roll your own numeric primary key, there is no point at all in having
an autonumber field as well.

 

[david epsom dot com dot au]

How To Implement Multi-user Custom Counters:
http://support.microsoft.com/?kbid=240317
http://support.microsoft.com/kb/q191253/

"Microsoft Jet has a read-cache [and] a lazy-write mechanism ... in certain
situations that require high concurrency, they may create problems."


(david)