[AccessD] Who is locking the record?

I wrote a function to test this code:

Function testIsLocked()
Dim db As dao.Database
Dim rst As dao.Recordset
Dim UserName As String
Dim MachineName As String
     Set db = dbDAO

     Set rst = db.OpenRecordset("tlkpClaimStatus")
     While Not rst.EOF
         Debug.Print "Record with PKID " & rst!CS_ID & " is locked: " & 
IsLocked(rst, UserName, MachineName)
         rst.MoveNext
     Wend
End Function

Notice the hardcoded table name.  I then went in to make sure that 
Access locked to the record level (Tools / options / Advanced), checked 
the box and exited the db.  Came back in, opened the table, started an 
edit of the first records but did not save, then ran the testIsLocked.

I got an error 3188 "locked by another session on this machine".

So I opened another session of the same application on the same machine. 
  This time I did get the 3268, but the error message is just "Could not 
update, currently locked."  The code failed (errored) because of the 
Instr(43,...) pointing past the end of the error string.

IOW, it don't work!  It might have in older versions of jet?

John W. Colby
www.ColbyConsulting.com


Jim Dettman wrote:
> Jennifer,
> 
> << So it seems that information is available, must be in the LDB file
> - does anyone know how to get at it?>>
> 
>   The information is not available in the LDB file.  The LDB file is simply
> a list of users currently in the database and in all cases, that may not be
> true.
> 
>   To really understand who is in the DB, you must look at the user locks
> placed on the .LDB file.
> 
> <<3218 is a record locking error, perhaps 3188 as well.  I can trap the
> error,
> but I don't know how to identify who is locking the record. >>
> 
>   The way to do it is to parse the message that Access generates.  Below is
> the code to do that.
> 
> Jim.
> 
> Function IsLocked(rs As Recordset, UserName As String, MachineName As
> String)
> ' Accepts: a recordset and two string variables
> ' Purpose: determines if the current record in the recordset is locked,
> '          and if so who has it locked.
> ' Returns: True if current record is locked (and sets UserName
> '          and MachineName to the user with the lock).  False if the
> '          record isn't locked.
> ' From: Building Applications Chapter 12
>     
>     Dim ErrorString As String
>     Dim MachineNameStart As Integer
>     
>     IsLocked = False
>     On Error GoTo IsLockedError
>     rs.Edit                     'Try to edit the current record in the
> recordset.
>     rs.MoveNext
>     rs.MovePrevious
>     Exit Function               'No error, so return False.
> 
> IsLockedError:
>     If Err = 3260 Then          'Record is locked -- parse error string.
>         ErrorString = Error$
>         UserName = Mid$(ErrorString, 44, InStr(44, ErrorString, "'") - 44)
>         If UserName = "" Then UserName = "(unknown)"
>         MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
>         MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) -
> MachineNameStart - 1)
>         If MachineName = "" Then MachineName = "(unknown)"
>         IsLocked = True
>     End If
>     
>     Exit Function
> 
> End Function
> 
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer Gross
> Sent: Tuesday, May 20, 2008 3:24 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Who is locking the record?
> 
> No takers on this?  Sometimes the Access message is generic and just lets
> you know the record is locked.  Other times when a record is locked Access
> will display their own message letting the user know who is locking the
> record.  So it seems that information is available, must be in the LDB file
> - does anyone know how to get at it? 
> 
> 3218 is a record locking error, perhaps 3188 as well.  I can trap the error,
> but I don't know how to identify who is locking the record. 
> 
> Jennifer
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer Gross
> Sent: Friday, May 16, 2008 1:13 PM
> To: AccessD List
> Subject: [AccessD] Who is locking the record?
> 
> Hi All,
> 
> In a multi-user split database using Access security, using the statement
> 
> Currentdb.Execute SQLUpdateQueryName, dbFailOnError
> 
> When it generates the error 3218 that a record is locked, how can I find out
> which user is locking that record?
> 
> Is there a better way to do this?  What I want to do is know that the
> operation failed and let the user know which other user they need to go to
> and ask them to close a screen, or whatever.
> 
> Thanks in advance,
> 
> Jennifer
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>