record locking. I've never dealt with this before...any tips

[mahaMutant]

Running an access 2000 .mdb
I have a customers form, which is used to enter new customers by multiple users at the same time. How do I prevent 2 users from entering a new customer at the same time.

The customersId is generated by code, which looks at the last number and generates the next available one.

Users tend to open the form, start completing the fields, get a phone call, then complete the fields. In the meantime, another users has started on a new customer...

How do I deal with this. I assume we are talking record locking. I've never dealt with this before...any tips to start with.
Thanks ] always a sucker to the greatest lie of all: will only take a minute [

 

[RickSpr]

There are two basic ways of going about generating a unique identifier in a multiuser environment, so first you have to pick one of these:
1. Generate the number when the user begins to create the record, or
2. Generate the number when the new record is actually being added to the table.

Technique 1 allows you to place the new identifier on the form so the user can see it throughout the data entry. However, the generated number is obtained from a 'seed' in the database, incremented, and stored back, so that the next number will be different. In order to avoid keeping that seed locked, which would block other users until the new record was added, you need to release the lock as soon as you've updated it. The problem with that is, if your user cancels out of adding the new record after all, the generated number has already been consumed and will never be assigned to a record. Thus, technique 1 has the problem that you can't guarantee consecutive numbers. This usually isn't a problem, though, and that's why Access' Autonum datatype uses this technique.

Technique 2 avoids this by not generating the number until you're ready to insert the new data. Since you aren't waiting for the user any more, you can leave the seed locked while you complete the insert, without holding up other users. But technique 2 also has a problem: you can't show the generated identifier until after the user has saved the record. Usually, that means you have to go through one more user interaction (form or MsgBox).

So your decision about which technique to use has to be made based on whether you require consecutively assigned numbers, and the relative importance of minimizing user interactions. If you choose technique 1, there's really no reason not to use an Autonum field in the table, unless the identifier has to have some specific structure (such as starting with a branch number, for instance). Also, if you use an Autonum, the record locking on the seed is done by Access, and you don't have to worry about it at all.

If you decide on technique 2, you'll have to have code to update the seed value (which you already have, I realize). You must use a DAO Recordset object, and specify pessimistic locking as an option in the OpenRecordset method call. (Actually, you could use optimistic locking too, but you'd have to write more code, and there's really no reason to since you're going to execute quickly. Optimistic locking is basically for when you have a relatively long delay between reading the data and updating it.) You can't use an UPDATE SQL statement for this purpose without risking conflict, because the lock that happens that way (which is out of your control) doesn't persist long enough for you to both retrieve and update the value. The retrieval and update must occur under the same lock.

So to answer your question, if you're already using a Recordset, just make sure you specify pessimistic locking when you open it, and issue the .Edit method call before you retrieve the value. (Don't retrieve the number before the .Edit call, or you may get a value that another user is in the process of updating. You don't have the record locked until you do the .Edit call.)

If you're not using a Recordset, you need to rewrite your code to use one, or switch to using an Autonum field. Rick Sprague