Multiuser record lock query?

From: Gitendra Proctor (gproctor_at_ihug.com.au)
Date: 03/28/04 

Date: Sat, 27 Mar 2004 19:41:06 -0800

Hi

I have a routine that generates an unique sequential value that I use for a key in an table, see code if needed below. Since I am in a multiuser enviroment I use the BeginTrans & ComitTrans to lock a seperate database so that I dont get two identical keys.

QUERY 1:
Is this the best way to do this? Am I doing the right thing?

QUERY 2:
What happens if user 2 enters this code while user 1 is in the transaction. Should I check if a transaction is taking place and exit (I'll take some action in this event like: re-try again in a loop x times, or give a message "system busy, try again in a few seconds, etc.,). If so, how do I check for this condition?

I'm using Visual Basic 6, talking to an Access 2003 database.

Thanking you in advance for your help.

Gitendra
gproctor@ihug.com.au

------------------CODE-----------------

Public Function lngGetNextCustomerNumber() As Long

  Dim rs As ADODB.Recordset 'Auto Customer Numbers

  '
  On Error GoTo LocalError
  '
  Set rs = deIndexes.rsCustomerNumberKey
  rs.Open

  deIndexes.conIndexes.BeginTrans 'Begin lock.

  If rs.RecordCount = 0 Then
    rs.AddNew
    lngGetNextCustomerNumber = 1
  Else
    rs.MoveFirst
    lngGetNextCustomerNumber = rs.Fields("CustomerNumber") + 1
  End If

  rs.Fields("CustomerNumber") = lngGetNextCustomerNumber
  rs.Update

  deIndexes.conIndexes.CommitTrans 'End lock

  rs.Close
  Set rs = Nothing

  Exit Function

LocalError:
  With Err
    MsgBox CStr(.Number) & vbCrLf & .Description & vbCrLf & .Source, vbCritical, "modGlobal->lngGetNextCustomerNumber()"
  End With
End Function

Relevant Pages

  • Re: Count error
    ... I have taken that on board, thanking you very much. ... and not the entire lot in the database. ... > You then create a main form bound to the Job table, with a subform bound ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.formscoding)
  • Re: Restore document
    ... I did some research and found some recycle bin concept. ... Andy ... >> delete from the database or still keeps the data for a while in the ... >> Thanking you, ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Restore document
    ... > I did some research and found some recycle bin concept. ... >>> delete from the database or still keeps the data for a while in the ... >>> Thanking you, ... >>> Andy ...
    (microsoft.public.sharepoint.portalserver)
  • Remove Duplicates
    ... How to revove duplicate row entries in the database. ... Thanking all in advance ...
    (comp.databases.filemaker)