[AccessD] Who is locking the record?

I just tested on terminal server with two different sessions running and it
generates error 3260 which has the locking user information in the error
description.  

Jennifer

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer Gross
Sent: Tuesday, May 20, 2008 2:27 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Who is locking the record?

I've done some testing with the code as well.  When the same machine is
involved I also get error 3188 or 3218.  A record lock generates error code
3260 if two different machines are involved (it seems that that is the
criteria).  I am not sure why the difference.  It is only error code 3260
that give you the user and machine name.  The other two either give "locked
by another session on this machine" or "record locked:  cannot update"

Jennifer

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, May 20, 2008 1:21 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
> 
> 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com