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

If you're storing both the zip and the client sequence number in a single account number field, you would have to split them apart to figure out the next sequence number in a given zip code.

It should be simpler to store them in 2 fields:

zipcode   sequence_num
92111     4
92111     5

Then derive your account number field with a query whenever you need it.

SELECT zipcode & "-" & sequence_num AS acct_num
FROM YourTable;

Then when you need to determine the next sequence_num, lngNextSequenceNum, within a given zipcode, pZip:

lngNextSequenceNum = DMax("sequence_num", "YourTable", "zipcode = " & pZip) +1

That approach can work fine for a single user application. If your application is multi-user, you need something more refined. However, that requirement exists whether you store "account number" as a single field or split it between two fields.

See Create and Use Flexible AutoNumber Fields for a multi-user approach.

Answer 2

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.