Create unique sequential number in multiuser environment

spikepl

Eledittingent Beliped
Local time
Today, 18:04
Joined
Nov 3, 2010
Messages
6,142
I have inherited a DB, where the following immutable requirement exists:

A unique "Key" (text) needs to be created for a record, not at the time of creation of the record itself but later. All this in a multiuser environment, so until such time that the key is created for the given record, mutiple records may exist without anything in that field. (This I gather prevents my using Unique Index on the field.)

The Key itself consist of NameOfPeriod & CountOfRecordsInPeriodSoFar + 1, i.e. CountOfRecordsInPeriodSoFar starts with 0 for each new period, and is incremented by 1, each time a new key is created

The records do have an autonumber. The sequence in which the Key is assigned, is not related to the sequence in which the records are created.

Any advice on how to create and store the key, ensuring that it is unique and sequential in a multiuser environment?
 
Two options, depending on how heavy the usage is. The usual DMax() based methods will work, and in your situation would only leave a small window where multiple users could get the same value (milliseconds). The bullet proof method is to store the current or next number in a table. In your code, you open/lock that table, get the number, increment and save the new number to the table, and release it. The lock ensures only one person can access that table at a time. There are some thoughts on the subject here:

http://www.baldyweb.com/CustomAutonumber.htm
 
Thanks for your input.

I am a firm believer in Murphy's Law, so I'd be inclined to do the bullet-proof version :)

I haven't worked with locking of tables before - would you happen to have a pointer towards some code? I guesss my problem is not unique, and some error handlign alse needs to be implemented to deal with the user who encounters a locked table .."Oops, try again in a millisecond" or something of that sort.
 
funny that - we used the method descibed in Chapman/Baron book (I think it was that one) to open the number register directly, as indicated above.

it worked in A97 - but then errored in A2003 (although the be was SQL, not Jet)

we ended up having to implement it in a slightly modified way.
 
I have googled and googled - can anyone help out with some code? I can eventually get there myself, but find no joy in boldly stepping on "prestepped" mines, so to speak :)

Specifically: how to open a linked table (ie. in the backend) locked for access by anybody else for the duration of retrieving the old max id, calculating the new one, and storing it back in the table.
 
Last edited:
Hmm.. I have stepped on a mine :-(

The code below executes fine, sort of.

When testing with two frontends, I step through one, halt on the line "myYear", and run the same sub in the other frontend.

I then run the first one to the end, and the other front end comes up with either Success (from the MsgBOX), or "error 3027 - Cannot update. Database or object is read only."

Aha - so I add to my error handler :

Case 3027
Resume

and the bl.... thing goes off into an infinite loop next time.

Any suggestions?


Code:
Private Sub cmdNewInvoiceNumber_Click()
    Dim db As DAO.Database
    Dim rstSource As DAO.Recordset
    Dim myYear As Long
    Dim myInvoiceNumber As Long
    
    On Error GoTo ErrorHandler
    
    Set db = CurrentDb()
    Set rstSource = db.OpenRecordset("tblMaxInvoiceNumber", dbOpenDynaset, dbDenyRead + dbDenyWrite)
    rstSource.movefirst
    myYear = rstSource(1)
    
    'if current date is in the same year as last invoice number, we just increment the invoice number
    If myYear = DatePart("yyyy", Date) Then
        myInvoiceNumber = rstSource(0) + 1
        rstSource.edit
        rstSource(0) = myInvoiceNumber
        rstSource.update
    Else ' we are in a new year, so we start the invoice numbering from 1, and save the new year
        myInvoiceNumber = 1
        rstSouce.edit
        rstSource(0) = myInvoiceNumber
        rstSource(1) = myYear
        rstSource.update
    End If
    
    MsgBox ("Success")
PrematureEnd:


    Set rstSource = Nothing
    Set db = Nothing

    Exit Sub

ErrorHandler:

    Select Case Err

        Case 3211
           ' MsgBox CStr(Err) & " " & Err.Description, vbExclamation
            'someone else is accessing the table so we failed, and just keep looping until we succeed
            Resume
        
        Case Else
            MsgBox CStr(Err) & " " & Err.Description, vbExclamation
    
    End Select

    GoTo PrematureEnd
    

End Sub
 
I maintain an MSAccess table that holds records for the next sequential number for a variety of items. There is not a record for each item, just the next number to be issued for each item type. I don't want to lock the table, so I added a long integer column that holds a random number. When a user needs the next sequential value for an item, he retrieves it and then tries to update the record using the item type and the random number as key values. The update includes the new sequential value (old value + 1) and a new random number.

If the record is not found, (someone else got the same number first) then the pgm loops, picking out the new next-sequential-value and the last user's random number and the sequence is repeated n times before giving up.

Works for me.

HTH,

Bill
 

Users who are viewing this thread

Back
Top Bottom