Record locking not working...what am i missing??? 1

[Moss100]

I have tried adjusting the record locking in options, but my original problem as below still remains. Any ideas what i'm missing here??? Thanks...

I have a database that is shared between 6 people. Occasionally two users will access the same record and one user will edit it, or perhaps sometimes both users are editing it at the same time. When the user goes to close the record access puts up a message saying that another user has aleterd the record and provides a few options, something like 'save changes' 'drop changes' etc. Often this seems to lead to database corruption and loss of the record. Is there a way of only allowing one person to edit a record or indeed only allow one person to view an indiviual record at one time. Ideally I would like to let two persons be able to access the record at one time, but I am keen to avoid these data corruption problems. Any ideas, thanks, Mark.

 

[misscrf]

In the tools options, use record level locking and edited record. I know there is something about pessimistic and optimistic locking, but I am not sure where to find it.
HTH

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Moss100]

Thanks, but i've tried this already. I have treid it on all machine and databases, both back end and front end, but it doesn't seem to change anything. I'm puzzled!

 

[payback]

I use a lock table and prevent any users from opening a record that is already opened. It is written in VB. Let me know if you need some ideas.

 

[payback]

Sorry, I meant VBA.

 

[Moss100]

Any help would be great - my programming skills are pretty basic outside of the Access environs. Thank you very much

 

[barny2006]

payback,
i have a similar situation. i would like to have an idea what this vba could do, so i can use it in my app to lock records. as moss100 said, the recordlocking option and edited record is not working in my case either. thanks.

 

[payback]

Sorry for the long time in replying.

I have a table set up called Locking_Table. The fields are -

ID Autonumber
Table_Name Text
Record_ID Long Integer
User_Name Text
When_Locked Date/Time

In each form where I need it, in the form's Open event I have -

        'Check to see if record locked by another user, else lock
        If LockRecord(strTableName, lngID) = True Then
            DoCmd.CancelEvent
            Exit Sub
        End If

which uses this function to check to see if the record has been locked by another user, else it locks it.

Function LockRecord(strTableName, strTableKey) As Boolean
'On Error GoTo Errorhandler

    Exit Function
    
    Dim rst As New ADODB.Recordset
    
    Dim strCriteria As String
    
    Dim msg, style
    
    LockRecord = False
    strCriteria = "SELECT * FROM Locking_Table WHERE TableName = '" & strTableName & "' AND RecordID = '" & strTableKey & "';"
    rst.Open strCriteria, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
        
    If rst.RecordCount = 0 Then
        rst.AddNew
        rst("ID") = GetNextID("Locking_Table", "ID")
        rst("Table_Name") = strTableName
        rst("Record_ID") = strTableKey
        rst("User_Name") = CurrentUserName
        rst("When_Locked") = Now
        rst.Update
    Else
        msg = "This record has already been locked by " & rst("User_Name") & "."
        style = vbOKOnly + vbInformation
        MsgBox msg, style
        LockRecord = True
    End If
    
    rst.Close
    
    Exit Function
    
Errorhandler:
    Call Error_Display_Vars(Err, Application.CurrentObjectName)

End Function

Then to unlock the record, in the form's Close event use -

Private Sub Form_Close()
On Error GoTo Errorhandler

    Call UnLockRecord(strTableName, ID)
    
    Exit Sub
    
Errorhandler:
    Call Error_Display_Vars(Err, Application.CurrentObjectName)

End Sub

which calls -

Function UnLockRecord(strTableName, strTableKey)
'On Error GoTo Errorhandler

    Exit Function
    
    Dim strCriteria As String
    
    strCriteria = "DELETE Locking_Table WHERE TableName = '" & strTableName & "' AND RecordID = '" & strTableKey & "';"
    CurrentProject.Connection.Execute strCriteria
    
    Exit Function
    
Errorhandler:
    Call Error_Display_Vars(Err, Application.CurrentObjectName)

End Function