Re: Multiuser record lock query?

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 03/29/04 

Date: Mon, 29 Mar 2004 12:24:12 -0800

And why aren't you using an Identity column for this? 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Gitendra Proctor" <gproctor@ihug.com.au> wrote in message
news:87ECB5F2-5092-4A4D-AA2F-A6D3F4D2F842@microsoft.com...
> 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
>
>