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

 

[tonyflavell]

Glad to have helped!

Regards

Tony