Split access database not locking row on edit

[nalmond]

I have a locking problem with a simple split access database. There are three users, on the corporate network, that need to edit / add / update records on a table in the back end database. To enable them to do this I put together an unbound edit form, which works fine for editing and adding etc. The edit form is displayed and picks up the record ID number from a datasheet on a subform when a control button is clicked. A recordset is used to populate the textboxes on the edit form.

The crux of the problem is that when I try to introduce row level locking, to prevent concurrency issues, the users can still edit the same record at the same time, introducing the last to save wins situation. I have set the tables and backend database to default to row-level locking and open the recordset using the following code to try and instigate pessimistic locking:

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=K:\PO_Tracker\PO_Tracker_v5_be.mdb"
.Mode = adModeReadWrite
.Open
End With

strSQL = "SELECT * FROM [PO Cost Monitoring] WHERE PO_ID = " & REC_ID & ";"

Set rst = New ADODB.Recordset

rst.Open strSQL, cnn, adOpenDynamic, adLockPessimistic
'Confirm locktype and edit mode
MsgBox rst.LockType & " " & rst.EditMode
Form_frmEdit_Record.Visible = True

Strangely a locking error is thrown when adding a new record, which is what I was hopign for when editing a reord. The same form is used for adding except the fields are empty.

I'm sure I'm just missing something simple here!

 

[markgrizzle]

You might try adding a flag field to the table, and setting it when a user begins editing a record, releasing when complete.

You'll also need to add code to check the condition of the flag before allowing users to edit any record.

Lastly, can't you set pessimistic locking for the application using the Tools | Options |Advanced tab settings?

 

[nalmond]

Thanks for the response markgrizzle.
Both front and back-ends were already set to open mode = exclusive, default record level locking = edited record and open databases using record level locking was checked.

Your first suggestion sounds interesting but I'm a little unsure how a flag field operates. Is it a boolean that you set to true just after you open the recordset, for editing, if it hasn't already been set to true by another user?

 

[nalmond]

I've got the flag field up and running and it's working a treat. Thanks for the advice.