Custom "auto"-number, multiuser environment

[spikepl]

To create some unique incrementing custom number, two methods seem to exist

1. On Form Update event for the record, use Dmax to retrieve the current max and assign it to the record, if record does not yet possess that unique ID.

2. Maintain a table with the max. Lock table, retrieve max, increment max, apply to record, save max, release table. Requires dbTable in OpenRecordset to be able to lock. dbTable requires separate opening of table in Backend from frontend, and some retrial strategy, eg.
http://books.google.com/books?id=qtgInMAAA0oC&pg=PT312&lpg=PT312&dq=access+dbDenyWrite&source=bl&ots=JkrBufTiiV&sig=OHdf3HSqo8jMIS0eb1NgZXWGAn0&hl=en&ei=hP-KTv2bI9DQ4QSL-9WBBA&sa=X&oi=book_result&ct=result&resnum=8&ved=0CGcQ6AEwBw#v=onepage&q=access%20dbDenyWrite&f=false

Ad 1. This seems to work in most cases - I guess the room for a separate user inserting the same value is counted in microseconds. But it does seem to exist, and so Murphy lurks ...doesn't he?

Ad 2. This is a fully secure belt-and-braces approach, as far as I can tell. But much more code to maintain.

Anyone with any comments on how secure method 1 is ? A little transparent error handling in case of duplicate key found would seem sufficient to close the millisecond gap.

 

[CBrighton]

I'd use the first method (or a recordset returning the top 1 result, sorted by the field in question - this gives the same result of teh highest number currently in use).

If possible you want to calculate the number as early as possible and save it as soon as possible. This reduces the possibility of duplications, however it's dependant on you not having other required fields, otherwise it can't be saved until they are input too.

 

[spikepl]

My issue is not really to reduce the possibility of duplication, but to exclude it alltogether. If two users initiate a record update milliseconds apart, user 2 could theoreticaly retrieve the same max as user 1, if user 1's form did retrieve the max value but did not yet finish save. I guess one could catch the error and redo the max-thing.

 

[CBrighton]

Then why not use a proper autonumber field?

I'm sure there is a reason, otherwise you wouldn't have gone to the hassle of doing it manually.

I assume it's a formatting issue, i.e. you don't just want "1, 2, 3, etc", can you give a couple of example numbers in the format which you use?

 

[spikepl]

I have two numbers that I need to secure 100% : order number and invoice number. These numbers are for human consumption, and sequentially incrementing numbers are fine (so no fancy constructed number). My underlying reason is to have numbers secured against potential Access flips in seed/sequence/sign etc. for ever after.

 

[CBrighton]

I'm not sure what you mean by access flips in seed/sequence/sign.

And I'm assuming that by "secure 100%" you mean have a 100% certainty of being a unique number?

 

[spikepl]

I want to be sure that the number is positive, sequential and incremented by 1 for each new record. Always. Access autonumbers do not provide this guarantee.

With "secure 100%" I mean sequential, incremented by 1 each time and unique, and this being valid today and also 5 years from now.

 

[CBrighton]

Barring things like corruption and deleted records (which would effect manually generated numbers just as much as autonumbers) autonumbers should do what you want. Plus it's generated at the precise moment of record creation rather than fetching the number, creating a record and then saving it.


I have yet to have an issue with autonumbers failing to do any of your requirements across my dozens of live databases or any of my development versions.

 

[spikepl]

I have a number of db's too, using autonumbers without any problems. But I do scan the web, and it seems that things still can glitch in some circumstances.

 

[CBrighton]

If you know of a part of Access which is glitch-proof, let me know.

 

[spikepl]

Hehe..

My pint (er, "Point", at this time of day) is more that the advice frequently meted out here and elsewhere is that autonumbers should not be used for anything other than relations/primary key, but not for human consumption, because of their alleggedly erratic behaviour. Perhaps the code has improved with time, and all the coding veterans are stuck in the past?

See fx a recent discussion here: http://www.utteraccess.com/forum/Autonumber-Increment-Ber-t1974207.html

 

[CBrighton]

Then I stand corrected.

Given that you can't get the max number, add 1 to it, create a record, use the max+1 as the value and save the record instantly I guess you have 2 choices.

1. Deal with the slight possibility that 2 people will click the new record button at the same time.

2. Have each new record lock the table to ensure no one else can save the record.

Although If you check the number at the beginning of the process and the end of the process, that may help. But even then in the highly unlikely scenario that 2 people click the "add record" button at the same time and then the "save" button at the same time it could still be duplicated.

 

[spikepl]

So we are back to my two methods in #1

Since the user does not care what number he gets, as long it's incremented by 1 and unique, I reckon I could intercept any potential wailing from Access proclaiming key violation in my indexed key field (if two users conspired to do their deed separated by the necessary milliseconds) , get a new max, and then save. Still easier than doing the table locking, it seems.

 

[CBrighton]

Certainly better for the end user than record locking.

 

[spikepl]

So now I have implemented it, with the error handler (responding to wail number 3022, if the order number already exists) getting Dmax one more time and saving the record again.