proper way of updating sql server table using access front end

Question

i have a front end in access and back end is sql server 2008

one of the fields is the account number and here are the rules

it is a zipcode like 92111 plus a dash plus a number.

so the first one would be 92111-1, the second 92111-2

this has to do with how many clients we have in the zip code

i would like this zip code to be automatically generated. here is what i need:

1. the user enters the zip code
2. i have a stored procedure that checks if this zip code exists already, to increment it: if 92111-4 exists already, then make it 92111-5.

what is the proper way of doing this?

Answer 1

I agree with HansUp that you should keep the ZIP and sequence separated. You can create an indexed computed field called AccountNumber which joins the zip and sequence.

The only way I know of doing this is locking the table, selecting max(sequence) ... where ZIP = 12345, inserting the new record, then unlocking the table. However, locking the table means everyone else has to wait, greatly affecting scalability. I can't recommend this, but I don't have another solution.