Autonumbers - multiuser application

[ZOR]

I was doing a search for something else and came across a thread in which it stated its unwise to use Autonumbers in a multiuser environment. If thats the case then I have been wasting my time on what I'm doing now. I would think 5-10 users may be using the database, I intend to split it with front/back ends. I know there can be problems with many users, but I thought I could get away with the standard Autonumber/Foreign key arrangement on tables. Can someone tell me the good/bad news on this. Thanks

 

[JoeAtWork]

I think the threads you read were talking about when people make up their own auto-numbering system. The built-in autonumbering works well with multi-users, I've used it in many applications and have never had a duplicate.

The problem many people have when they make their own autonumbering is that they will take the current largest number and add one for their new ID - but then they don't save it right away. So in the meantime, somebody else could also start a new record and get the same ID. The person who saves first is OK, but the second person who tries to save gets an error.

This is not a problem with the built-in autonumber, because Access "uses up" that number as soon as somebody starts a new record. Even if the user cancels the new record, that number does not go back into the pool.

 

[ZOR]

Many thanks. I will try and find where I read it, it was a Tek-Tips thread regarding not to use Autonumbers I thought, and suggested a way to create a numbering system in a seperate table. Will get back after searching. Thanks for the confidence building re using whats there.

 

[MichaelRed]

Autonumbers have been shown to be corruptable in multiuser systems. There is a FAQ regarding this. Most "Multiuser" systems do not encounter a problem because the (multi)-users are not actively entering data. Ms. may have corrected the problem, as the faq is from several years back, but was based on experience and a fair amonut of or contrived testing. I used three "users" attempting to enter data at as near simultaneously as possible. In that scenario, there was approx a 20% chance that the system would create problems. The most typical was to have the db 'crash' and need ye olde compact and repair remedy, Other unkind events also occured.



MichaelRed

 

[BDW1969]

ZOR,

I have a db with the front/back split like you say you will do, I have been using it for 5 years with anywhere from 10 - 20 users and have no issues with the autonumbers.

 

[JoeAtWork]

Well, I can think of at least a dozen applications I've worked with that have at least 5 users editing or adding data at the same time (at least during the busier times of the day). These were systems like point-of-sales, inventory tracking, appointment scheduling. They were not used for intensive data entry, i.e. all users dedicated to adding new records all the time. So maybe there's a problem with that type of application that I'm not aware of. I'd be interested in seeing any articles on this subject.

Every once in a while I've had to fix a corrupted application. I've never had reason to point at autonumbering as the culprit (I'll admit though that usually I never find a cause - it's Access after all and anyone who's worked a lot with it knows it's not 100% reliable).