Multiple user Sqlserver backend help

[Skater]

I have an access database connected to Sqlserver. There are multiple users, each able to create new records in table tbl_Code. The PK is Code and it is incremented in VBA based on the previous Code [Max(Code) + 1]. I have only had it happen a few times, that more than one user has created the same Code in tbl_Code.
Now, I have been tasked with allowing the user to enter multiple codes in one transaction. How can I do this? My db skill are good, but not that good.

Thank you in advance

 

[CJ_London]

Why not use an autonumber field type?

 

[GPGeorge]

It's called Identity in SQL Server, but it is the same thing, functionally, as the AutoNumber in Access.

 

[Pat Hartman]

I have only had it happen a few times, that more than one user has created the same Code in tbl_Code.

1. You need a unique index on the sequence number.
2. A code generated by you has the same problem as an autonumber and that is why the autonumber is generated and saved FIRST - to eliminate duplicates. When you generate your own, you need to do it as the last thing in the form's BeforeUpdate event and do it as a loop. If the insert fails, increment and insert again. An autonumber/identity would be simpler.

Now, I have been tasked with allowing the user to enter multiple codes in one transaction. How can I do this? My db skill are good, but not that good.

All the more reason to use an autonumber. But you only insert one row at a time so the technique above doesn't change.

 

[gemma-the-husky]

The right way to do this is to get the next number at the very last moment when you are committed to storing the record. That way you don't lose the sequential number if you decide to cancel the edit.

If you need multiple numbers then you need a loop of some sort to obtain the numbers.

That's what @Pat Hartman said above.

You can store the next number in a table, rather than using an autonumber. However, if you want to be sure that other users absolutely can't read the same number than you need to lock the table before you do the read, then read and increment the counter, and then release the lock. I imagine that's what happens with an autonumber, but we don't see that code. You can do this relatively easily with an access table, but I'm not sure how you get a temporary lock with a SQL table. You also have to be sure two conflicting users don't cause a deadly embrace, which is another reason to not retain locks for an extended period.

Because of all this if you read the next number then abandon your edit, the next number seed has already been incremented, and your sequence won't be intact. So you can't easily see and examine the generated number, but also maintain an intact sequence without controlling the user process carefully. That's the biggest/underlying issue.