Handling Multiple User Updates

Hi, i dont know if this is the right place for this.
Could anyone help me with this.

The Scenario :

There would be say ten users. Each of the user is doing database communication i.e., adding a record to a particular table at the same time and for the record to be added a unique id has to be generated thru code (something like "USER00001"). Now for this, i generally take the maximum of the number ("?????" from "USER?????"). The problem is how to handle the situation since all the ten users want to update at the more or less same time. How would the record-locking be handled.

I have the least idea how to handle this.
Please, i would be thankful if you can mail the response to gantibabu@yahoo.com

Waiting in anticipation...vijay.

[HunterMcCray]

I do not see any reason that you need to worry about record locking. If you have ten users adding new records then you have ten users working on ten different records. I assume that you are using autonumber as a primary key?(if not this should simplify things) and that you are storing the UserId with the data? Simply use a query to limit the recordset that any one user is using to His/Her UserId. Use an SQL statement to add the record with the default data or your actual data depending on the circumstances, then refresh the recordset limited by the UserId and then use the MoveLast method to find out what the PrimaryKey is (of course the most recent field added by any given userid is the last record in the recordset.)

Hope it helps,

Hunter

Hi HunterMcCray, thanx for the reply. What you told is ok if i am using the autonumber but i am not and what i want to use as a primary key is say empid which would be like "EMP0001" then if i want to add a new record then i should use "EMP" & format("the maximum of the right 4 digits" + 1,"0000"). Thats it. Could you please help me with how i can do this in the scenario mentioned before. Thanx in Advance for the reply...vijay.

[HunterMcCray]

Create a table called "Users". Create a boolean field for each potential user and an autonumber that is your primary key. Set the default of the boolean field for each user to False. When a user requests a new entry update the first available field for that user to TRUE. Use the primary key number as your number. The only potential problem here is that you may occasionally recieve an error that the record you are trying to update is locked by another user, you must trap for this and loop until the record becomes unlocked. To avoid any possibility of this you might create a table for each user that only contains an autonumber field and a boolean field, then there can never be any conflict. This all seems like a great deal of trouble to work around what databases were designed to do.

Hunter