Row level locking - Check s tatus of row 1

[Jables]

My application uses row level record locking in a multiuser environment. Does anyone know how to check if a row is locked for editing?

 

[jrbarnett]

Hello,

Use

intStatus = syscmd (acSysCmdGetObjectState, acform, "form name&quot

intStatus can be one of three options:

acObjStateOpen - the object is open but not saved
acObjStateDirty - the current record has been changed but not saved
acObjStateNew - new record, not saved.

If it is 0, then the item is not open.

John

 

[Jables]

Say I want to code some custom error handling whenever the row is locked by another user. How would I need to set the options in the application in order to turn off Access handling the record locked condition?

I have the following code in the BeforeInsert event of the form where record locks might occur:

Private Sub Form_BeforeInsert(Cancel As Integer)

    Dim RowStatus As Integer
    
On Error GoTo Err_BeforeInsert
    
    RowStatus = SysCmd(acSysCmdGetObjectState, acForm, "frmtime subform") 'Should check the rowstate
    
    If RowStatus = 0 Then 'If RowStatus=0 then the record is not locked
    
        'Do some code if the row is not locked

    Else
    
        'Display a custom message
        'detailing which machine has the record locked.
        'Keep checking if to see if the record is still locked
        'Notify the user when the record becomes available
        
    End If
    
Exit_BeforeInsert:
    Exit Sub

Err_BeforeInsert:
    MsgBox "An error has occured when trying ot set the value of the Date Worked field." & vbCrLf & _
        "Error Number: " & Err.Number & vbCrLf & _
        "Error Message: " & Err.Description
    Resume Exit_BeforeInsert
    
    
End Sub

Any ideas on how I could check to see when the record becomes available? Some kind of While loop maybe that keeps checking the RowState until it's zero, maybe?

 

[jrbarnett]

Access has a built in multi user row level locking scheme - you don't need to write any specific code to use it.
The actual behaviour is determined by the "Record Locks " property of the form, which is on the Data tab.
If you set it to No Locks, this will give the most concurrency, and Access will only lock the record for the fraction of a second it takes to update a record or add a new one.
Easiest way to try it is to open the same database on two separate computers simultaneouly, then change some data from one machine but before saving it, open it up on the other machine and try to edit - and see what happens.

John

 

[Jables]

I guess the problem I'm having is that I need to use Row Level Locking (Pessimistic), but say two users try to simultaneously add a row of data. This causes their computers to freeze and eventually display the message, "This record is locked by user, %WindowsLogonName% on machine %MachineName%." The message, is fine, but I just want to avoid the problem of the machine freezing up for 30-45 seconds. I thought maybe a good way would be to use VBA to determine the lock status of the row and then prevent the user from adding/editing a record if it's locked.

These are the option values which I forcibly set using SetOption:

OLE/DDE Timeout (sec): 30
Refresh Interval (sec): 60
Number of update retries: 1
ODBC Refresh Interval (sec): 1500
Update retry interval (msec): 250