[AccessD] Who is locking the record?

Jim Dettman jimdettman at verizon.net
Wed May 21 09:51:35 CDT 2008


John,

  This code originally was published with Access 2.0 and will work as long
as error 3260 is returned. 

  I have not used it in many years, but when I did it worked and worked
reliably.  It is quite possible that additional error codes have been added
to JET for records being locked for other reasons.

   Certainly 3188 could be added easily as certainly you could return the
current machine name and user on this machine.

  But this is the only method I know of to return a user or machine name.
As Drew said, the JET locking white paper does have the math to determine
who has a record locked, but you need to know where the tables are in the
DB, so that's quite problematic.

  The white paper also only applies to JET 3.5 and prior.  There has been no
published information on JET 4.0 and I would assume with the addition of
record level locking for some operations that the errors returned for locked
records have been changed.

Jim.

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, May 20, 2008 5: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




More information about the AccessD mailing list