What user is locking a record??

[waynea]

Scenario:
In Access 2000, using edited record locking, one user has a record locked and wanders off while another user tries to edit the same record. Second user gets the locked record symbol. Is there an easy way to show the second user who is locking the record, like when two users try to save the same record using no locks??

 

[ErikZ]

Well, considering the effort it takes just to see who's logged into your database, I'm betting the answer is no.

 

[UncleJack]

Of course there's a way - but it won't be easy. Look on the web and in Access help sites for code that tears apart the ldb file. It will be in there, but I haven't a clue how to get it out. However, I'll bet someone has done it somewhere; all you have to do is find it.

Uncle Jack

 

[ErikZ]

I'm sorry Jack, but I went to the MS support site and found this:

http://support.microsoft.com/support/kb/articles/Q208/7/78.asp

It doesn't mention anything about what the user has open, let alone what records the user has open. If multiple users are accessing a database and one has a record open, I don't see how it is possible to track down who is accessing a particular record.

Hmmm, addendum. I found the MS white paper on "Understanding Microsoft Jet Locking".

http://www.microsoft.com/accessdev/articles/jetlund.htm

There are table-level locks, but not record level locks.

Well, it's better than nothing.

 

[UncleJack]

Found this statement in the M/S white paper on ldb files at

http://support.microsoft.com/support/kb/articles/Q208/7/78.asp

The .ldb File Usage
The Jet database engine uses .ldb file information to prevent users from writing data to pages that other users have locked and to determine who has other pages locked. If the Jet database engine detects a lock conflict with another user, it reads the .ldb file to get the computer and security name of the user who has the file or record locked.

This is a clear indication that you can determine who has what locked if you know enough about the ldb file structure and have the programming ability to tear it apart. I don't have a need so I don't have to worry about whether I can do it or not, but I'd bet I can't.

Uncle Jack

 

[ErikZ]

Is there a way to avoid this situation to begin with? Maybe copy a record into a form, and when you're done with it, copy it back? That way you never lock down the record.

 

[Soundsmith]

I have some routines (downloaded here, I think) that cause the user name to be stored in a table along with the record ID. Every time the user changes to a different record, the table gets updated, so when there is a record conflict, the user name can be returned.

As I have kind of kludged this together, and have not tested it in a production environment, I'm not at all sure that there aren't gaping holes in it. But I'll post what I can.

These routines are in a Module I call OpayUser:

Option Compare Database
Option Explicit
Public MyName As String
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

'-------------------------
Public Function GetUser()
    Dim sUser As String
    Dim sComputer As String
    Dim lpBuff As String * 1024

    'Get the Login User Name
    GetUserName lpBuff, Len(lpBuff)
    sUser = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
    lpBuff = ""
    GetUser = sUser
    MyName = sUser
    'Get the Computer Name, OPTIONAL, did not need it
'    GetComputerName lpBuff, Len(lpBuff)
'    sComputer = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
'    lpBuff = ""
'    MsgBox "Login User: " & sUser & vbCrLf & _
'           "Computer Name: " & sComputer
End Function

'--------------------------------------------- 
Public Function DeleteUser()
Dim usrNam, delStg, usrStg As String
    usrNam = MyName
    Call ReleaseRecs
    delStg = "DELETE * FROM tblUsers WHERE tblUsers.usrName = '" & usrNam & "'"
    DoCmd.RunSQL delStg
End Function

'------------------------------------
Public Function AddUser()
Dim usrNam, delStg, usrStg As String
    usrNam = GetUser
    delStg = "DELETE * FROM tblUsers WHERE tblUsers.usrName = '" & usrNam & "'"
    DoCmd.RunSQL delStg
    usrStg = "INSERT INTO tblUsers (usrName, logged) VALUES ('" & usrNam & "', Now)"
    DoCmd.RunSQL usrStg
End Function

'---------------------------------------------
Public Function ReleaseRecs()
Dim usrNam, delLox As String
    usrNam = MyName
    delLox = "DELETE * FROM tblLocks WHERE tblLocks.usrName = '" & usrNam & "'"
End Function

'------------------------------------------------
Public Function LockRecs(theNum As Long) As Boolean
Dim theLockSql, them As String
' if successful locking, return true, else msg who is locking

    DoCmd.RunSQL "UPDATE tblLocks Set editing = true where tblLocks.usrname='" & MyName & "';"
    If DCount("cnum", "tblLocks", "editing = True AND cnum=" & theClaim) > 1 Then
        DoCmd.RunSQL "UPDATE tblLocks Set editing = false where tblLocks.usrname='" & MyName & "';"
        them = DLookup("usrName", "tblLocks", "editing = True AND cnum=" & theClaim)
        LockRecs = False
        
        
        MsgBox "Those records are being edited by " & them & ". Try again later."
    Else
        LockRecs = True
    End If
End Function

'------------------------------------------------
Public Function UnLockRecs(theNum As Long) As Boolean
     DoCmd.RunSQL "UPDATE tblLocks Set editing = False where tblLocks.usrname='" & MyName & "';"
End Function

'---------------------------------------
Public Sub Closeout()
Dim usrNam, delStg As String
    usrNam = GetUser
    delStg = "DELETE * FROM tblUsers WHERE tblUsers.usrName = '" & usrNam & "'"
    DoCmd.RunSQL delStg
    delStg = "DELETE * FROM tblLocks WHERE tblLocks.usrName = '" & usrNam & "'"
    DoCmd.RunSQL delStg
End Sub

================================
Tables:
tblLocks
cnum - same name and type as primary key of table being locked
editing - True?False
useName - user's login name

tblUsers
usrName - users' login name
logged - Date-Time field for when users logged in.

In the switchboard open event, I do:

'add user name to tblUsers
call GetUser
call AddUser

In switchboard close, I call:
call CloseOut

In the primary form load, I include:

    If IsNull(MyName) Or Len(MyName) < 3 Then
        Call GetUser
        Call AddUser
    End If

just to be sure the user didn't bypass Switchboard

I have a command button to return to the Switchboard, in the click I include

        Call ReleaseRecs
        DoCmd.Close

The subform is responsible for allowing or disallowing edits and locking the records. I have buttons on the subform to Add or Edit child records. (In this app, only one person is allowed to change data on the main form, so these routines were not needed there.

In the cmdAddRec_click and cmdEditRec_click, I use:

    theClaim = Me.cnum ' THIS IS THE PRIMARY KEY

    'just to be really, really sure!
    If (IsNull(MyName) Or Len(MyName) < 4) Then
        Call GetUser
    End If

    If LockRecs(theClaim) Then
        ' process the addition or edit
        ' whatever processes are called, end with :
        call UnlockRecs

    Else
        ' records locked, so exit
        GoTo Exit_thisRoutine_Click
    End If

That's it. Any time I need to edit or add something, I just bracket it with an 'If LockRecs' condition.

As I said, this is NOT in production, and I've only been able to test it with myself as user running multiple instances of Access. MyName as a global may prove to be a problem, if it is, I'll add it to a local table and reference the table instead of the variable.

I don't know if this approach will be useful to you or not, but I hope it can give you some ideas.
David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[waynea]

I thought about that, but this is a fairly extensive database (not mine, by the way, my client's in-house guy put it together.) There are probably twenty or thirty tables and sixty forms, so I don't know how much trouble they'll be willing to go to. Thanks for the input.