Sequential Numbers in a multi-user application

[bradmaunsell]

Windows XP SP 2
Access 2000 and 2003

We have experienced problems with some of our older Access database applications. The issues relate to multi-users and sequential number assignment to records. These applications use Access .mdb (linked) tables. Auto number key is (intentionally) not used. For example, quotes and signed contracts are stored in the same table. A sequential number is not assigned to a quote unless it's status changes to a signed deal.

To prevent duplicate or whacko sequential numbers, I am thinking of using the following routine. This routine will be used for assigning policy numbers, transaction numbers, invoice numbers, etc.

I would appreciate any advice or comments on this solutions and/or access multi-user issue. I understand the "record locking" is not really single record locking in Access.

As you can see in the code, a separate (counter) table is used to record the last used sequence number for various functionality - policy numbers, transaction numbers, invoice numbers, etc.


Thanks
Brad

Sub test_NextSeqNumber()
MsgBox NextSeqNumber("tblSeqNumber_GL_Trans")
End Sub

Function NextSeqNumber(strTable As String) As Long
' 1/6/2005 BradMaunsell
Dim rsSEQ As New ADODB.Recordset

rsSEQ.Open strTable, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTable
If rsSEQ.BOF = True Then 'Only for first time ever run and no initial value was set
With rsSEQ
.AddNew
rsSEQ!SeqNum = 1
.Update
End With
End If
rsSEQ.MoveFirst
With rsSEQ
rsSEQ!SeqNum = rsSEQ!SeqNum + 1
.Update
End With
NextSeqNumber = rsSEQ!SeqNum
rsSEQ.Close
Set rsSEQ = Nothing
End Function

 

[alvechurchdata]

Looks good to me - just three comments:

I think it'll return a value of 2 the very first time it runs. Should that first Endif be an Else?

I'd go for Pessimistic locking to prevent any possibility of interleaved access.

Is there an On Error wrapped around this snippet to catch the case when a conflict does occur?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[bradmaunsell]

thanks for the feedback Geoff

Brad

 

[MichaelRed]

Kocking will be problematic.

See faq700-184.

ADO doesn't appear to provide an EXCLUSIVE lock for a table, so DAO is necessay to achieve it (Exclusive lock). The routine is somewhat more complex in generatinghte value than you indicate, the remainder is necessary to assure the retrievial of the values without returning an error to the user. Please not that this mixture of recordset types (ADO and DAO) requires the explicit identification of each declaration of db and rs to include the TYPE preefix.




MichaelRed

 

[bradmaunsell]

Your suggestion has shaken loose some old memories from my Fortran and Cobal days!

I created a routine using your code with some modifications to suit my needs and it works great! I tested my version with multiusers whacking at it and holding a table open and checked out OK.

Thanks, this gives me a tool to eliminate a lot of cussing in our office.

Brad

 

[MichaelRed]

" ... old memories from my Fortran ... days

Careful, You could be showing your age ...




MichaelRed

 

[MichaelRed]

... but also consider yourself at least willing. Many in htese fora were "overwhelmed" by that piece od code. Thanks for at least looking into it.





MichaelRed

 

[ThinWhiteDude]

Michael,

I wanted to revive this thread because I'd like to use your code but have a question.

My database (version 2002) has multiple users on a network. When one person begins entering a record, he may keep it "open" for over an hour. Will your code work for this kind of situation?

At present, I am generating what I hoped was a unique number by running a SQL statement when the user opens the form, it pulls out all the records for this year, gets the largest from a number field (lngNum), appends the current year's last 2 digits to the next incremented number, then puts this into the numID field:

Public Sub GetNewIDNum()

        Dim cnn As ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strSQL As String

        Set cnn = CurrentProject.Connection
    
    'Autonumbering variables
        Dim LastNum As Integer
        Dim NewNum As String
        Dim CurrentYear
        

        strSQL = "SELECT lngNum,dtmDate FROM tblObservations WHERE Year(dtmDate)=Year(Date()) ORDER BY lngNum"
        rs.Open strSQL, cnn, adOpenDynamic
        
        If Not rs.EOF Then
            rs.MoveLast   
            LastDate = rs("dtmDate")
            LastNum = rs("lngNum")
        Else
        End If

    'Get next counter number:
                NewNum = LastNum + 1
        
    'Set new counter number value
       Me.txtNumber.Text = NewNum

        rs.Close
        Set cnn = Nothing


     'Put new IDNUM in, by appending last 2 digits of current year to counter number
        Me.numID.Enabled = True
        Me.numID.Locked = False
        Me.numID.SetFocus
        Me.numID.Text = Me.txtNumber.Value & "-" & Right(Date, 2)
        
        Me.dtmDate.SetFocus


    
End Sub

txtNumber is actually hidden, and only numID is visible.

Because several people could be doing entries at the same time, I run another procedure when they close the form to check for duplicates, and if found, reruns the GetNewIDNum procedure:

        'Check to see if the number has been taken while record was being made:
        Dim cnn As ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strSQL As String
        Dim DupRecs As Integer
        Dim NewSORNum As String
        

        Set cnn = CurrentProject.Connection

        strSQL = "SELECT numID FROM tblObservations WHERE tblObservations.numID ='" & Me.numID & "'"
        rs.Open strSQL, cnn, adOpenStatic
        
        DupRecs = rs.RecordCount
        
        
        If DupRecs > 0 Then
            rs.Close        
            GetNewIDNum
            MsgBox "There are " & DupRecs & " other SORs with this number." & vbCrLf & "Your SOR has been given a new number." & vbCrLf & vbCrLf & "Your new SOR number is: " & Me.numID, vbOKOnly, "SOR Alert"
            DoCmd.RunCommand acCmdSaveRecord
        Else
        End If

I know this is awful not just because it's not working, and I'm still getting duplicates, but because this is so much duplicate work. The reason I do this twice is the client wants the users to see their new number when they start their report, not when they're done with it.

I am hoping your code will solve my problems, but wanted to state my scenario and see what you think. I appreciate any consideration you can give this.

Thanks,
TWD

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmm ... the infamous hemming and hawing routine ... mmmmmmmmmmmmmmmmm

You might review the thread. I believe it includes your question and its' answer already.

Alternatively, you might review the code (module?) in referenced in the faq, as that coulda, woulda, shoulda, at least provide the basis for your independent judgement.

You could just generate a test scenario in an environment similar to your application and conduct blind testing and base your use on the results.

Any (or even ALL) of which would provide a better answer than I can.



MichaelRed

 

[tonyflavell]

Hi there
Just noticed this thread. I developed a database for telephone sales for a local Health Spa. This system has 27 simultaneous users and after many trials the issue of duplicate records was eliminated by using the following code in the "OnLoad" attribute of the main entry form.

Set MyDb = CurrentDb
Set MyRS = MyDb.OpenRecordset("CounterTable", , dbDenyRead + dbDenyWrite)
MyRS.Edit
MyRS!NextAvailableCounter = MyRS!NextAvailableCounter + 1
VoucherID = MyRS!NextAvailableCounter
MyRS.Update
MyRS.Close
Set MyRS = Nothing
    
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRefresh

Because the incrementing counter (VoucherID] was assigned and saved on opening the form with locks on the Counter Table, no other user could access the table to obtain the next counter number before the update was complete.

Result not a single duplicate ID in 27,000 transactions!

Hope this helps

Tony

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmm

yes the lock is at least necessary, but without the adjuct of some mechanisim (perhaps a retry, on the table access?), it could result in other failure modes. In a form Open event, users might just get a error msg, and do the retry manually, ... but it that a good way to do it?

Further, the locking only applies to the "CurrentUser" (note -- as a KEYWORD), which implies the reqiuirement for Ms. A.'s securtiy

mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm ...




MichaelRed

 

[danvlas]

It's odd that no TRANSACTIONS are mentioned here...

Write all data in an UNBOUND form. Related 'records' could be stored in a listbox, in a grid, array, temorary table, whatever.

Put a command button to 'save' the info.
When clicking a button:

On Error GoTo Err_Handler
YourConnection.BeginTrans
'do whatever needs to be done
YourConnection.CommitTrans
Exit_Here:
'set all objects to NOTHING
Exit Sub
Err_Handler:
YourConnection.RollbackTrans
'Whatever needs to be shown to the user describing the error
Resume Exit_Here

The transaction will take care of locks, auto-numbers, sessions, everything...

One piece of advice: NEVER leave a transaction to the will of the user (message boxes, dialog forms, anything that could prevent it to succeed or fail independantly from user. Otherwise you lock the objects for other users for an unknown period of time...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[bradmaunsell]

Here is an update of the code I have in my lastest deliverables.

'4/24/2005 Brad Maunsell

I use a table named "zcntSeqSample" has one field named "SeqNum". I have several tables for various numbering of transaction numbers, invoice numbers, etc




Sub TestNextSeqNumber()
MsgBox NextSeqNumber("zcntSeqSample")
End Sub

Function NextSeqNumber(strTable As String) As Long
' 1/6/2005 BradMaunsell
' Created from example in Tek-Tips fag700-184

Const RiErr = 3000
Const LockErr = 3260
Const InUseErr = 3262
Const NumRetries = 20#

Dim dbSeq As DAO.Database
Dim rsSeq As DAO.Recordset
Dim NumLocks As Integer
Dim lngX As Long

On Error GoTo LockedByAnotherUser

Set dbSeq = CurrentDb()
Set rsSeq = dbSeq.OpenRecordset(strTable, dbDenyRead)

If rsSeq.BOF = True Then 'Only for first time ever run and no initial value was set
With rsSeq
.AddNew
rsSeq!SeqNum = 1
.Update
End With
NextSeqNumber = rsSeq!SeqNum
Else
rsSeq.MoveFirst
With rsSeq
rsSeq.Edit
rsSeq!SeqNum = rsSeq!SeqNum + 1
.Update
End With
End If
NextSeqNumber = rsSeq!SeqNum

NormExit:
rsSeq.Close
Set rsSeq = Nothing
dbSeq.Close
Set dbSeq = Nothing
Exit Function

LockedByAnotherUser:
If ((Err = InUseErr) Or (Err = LockErr) Or (Err = RiErr)) Then 'Check for expected errors
NumLocks = NumLocks + 1 'If expected error then increment counter
If NumLocks < NumRetries Then 'Do something to kill time while waiting for your turn to open table
For lngX = 1 To NumLocks ^ 2 * Int(Rnd * 20 + 5)
DoEvents
Next lngX
Resume Next 'Go Back and try again
Else
MsgBox "This error should never happen. Failure with NextSeqNumber function.", vbOKOnly & vbCritical, "CRITICAL LOCKING ERROR"
GoTo NormExit
End If
End If
End Function

 

[MichaelRed]

actually the transaction approach won't solve the issue here. At the start of the thread(s), the requirement is for the sequential assignment to be made at the very beginning of the transaction. I take liberty in the interpertation of the term "SEQUENTIAL" to mean without gaps. While this implies a rather juvinile approach to recordkeeping, it is all to common amongst many business types (particulally accounting). To both use the transaction function and assign the record at the beginning of the deata entry and maintain the sequential (no gaps) value AND not include a number of records which woould then need to be marled "Incomplete - disregard" or otherwise inactive would prreclude other users from continuing with their work (using the same number sequence.

Then, you can go all the way back to the concept that is oft expressed here: The generation of PK values should NOT involove the user. In many threads / posts, the designer / programmer is specifically cautioned to not use the PK (automumber included) as a user manipulated or even observed value. Its' ONLY use is / should be to assign the unique value to the record and potentially as the FK in a relationship (join).

From an even broader perspective, one might conclude that as the seperate requirements are piled on the operation, it becomes harder to accomopdate. At some point, one inevitably arrives at the locus of operations which are so constrained that it is simply not possible. While the requirements here are not (quite) at that level, they can easily serve to illustrate the issue.



MichaelRed

 

[tonyflavell]

Hi again

In practice, if the CounterTable in my earlier code is locked by a user, additional code to retry the CounterTable is not neccessary because "Number of Update Retries" is set to 4 at a "Update Refresh Interval" of 250 msec. This is easily far in excess of the time required to update the counter and free the locks for the next user.

As I said 27,000 transactions without duplication and without skipping a "VoucherID", in a user environment, speaks for itself!

Regards

Tony

 

[ThinWhiteDude]

I have been absent the weekend and have just now had a chance to take a look at these responses, which I must thank everyone for.

I have gone over your procedure, Mr. Red, and confess that although I understand it, tailoring it to my needs might be beyond my coding skills. I understand some of the basic requirements here: Locking the "new number delivering" table, providing a mechanism for retries, and error handling.

What I'd like to do is use your code, Tony, and incorporate some of Mr. Red's features with it. That's what I'm in the process of doing now and will report back to all with questions/comments/success story.

Thanks again to all,
TWD

 

[tonyflavell]

Hi

Look forward to your report!

Regards

Tony

 

[ThinWhiteDude]

Tony. . .

I installed the form with your loading code (plus standard error handler) on Friday, and would have heard about it by now if there'd been any problems with standard stuff. So things are looking good so far. I'll probably wait a couple of weeks to check for duplicates, as that's been as often as they creep in.

Will keep you posted.

Thanks,
TWD

 

[tonyflavell]

Fingers crossed. Thanks for the feedback, I appreciate it!

Regards

Tony

 

[ThinWhiteDude]

Tony. . .

Well, it's been 3 weeks and every time I run my query to check for dupes, it comes up empty. Would definitely have come up with a few by now using my old code, so it looks like your solution is a winner for my set-up.

Thanks so much for helping me!




TWD