Write Conflict while trying to save the record

[FancyPrairie]

I have an Access (2000) FE db linking to SQL tables (SQL 2005). I have a SQL stored procedure that is scheduled to run on the SQL server every 15 minutes to get some records (only records that have changed since the last time the check was performed).

Note that there are no locks on the record via the form because Access 2000 locks a page not record. So I can't lock the records. However, there is no chance of 2 users editing the same record at the same time.

Suppose the user is editing record #1 (via a form) and while editing the record the stored procedure executes and modifies record #1. The form (upon refreshing itself) overwrites what the user has changed. To try to get around the problem, in the OnCurrent event of the form I set the Refresh Interval to 0, then in the OnBeforeUpdate event of the form, I reread the record and compare it to the forms recordset. If there are differences, I change the values of the form's recordset. And then report to the user what I have done. Upon exiting the BeforeUpdate event, I get the "Write Conflict" error message and I either have to save the changes to a buffer or drop them. But I want to save them, since I've fixed the "conflict".

How do I prevent the Write Conflict error from appearing?


Note that the changes the stored procedure makes to the record are to fields that the user would normally not edit, unless they were adding a record that the stored procedure would never access.

 

[genomon]

If you simply want to suppress the error, trap it in an error handler.

Private Sub YourSub()

On Error GoTo YourError

'process code here

OPEN_EXIT:
    Exit Sub
    
YourError:
    MsgBox "error " & Err.Number & " " & Err.Description
    If Err = ?? Then Err.Clear
    Resume OPEN_EXIT

End Sub

You will of course want to replace ?? with the correct error number...

Ni neart go cur le cheile.