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.

 

[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.