RowVersion / TIMESTAMP and Optimistic Locking in Access

  • Thread starter Thread starter rmanchu
  • Start date Start date
R

rmanchu

hi

regarding subject; is there any way to control how Access handles a
concurrent modification?

Access shows a dialog box with options for Save Changes, Copy to
Clipboard, and Drop Changes.

my requirement is to disable Save Changes; ie, under no circumstance
should another users modification be overwritten.

can this be done?

if not, what's the alternative method?


thanx
riyaz
 
riyaz:

If one user attempts to save a record which has been changed by another user
since user 1 began editing it Error number 7787 is raised so you should
handle this in the form’s Error event procedure, popping up a custom message
box to inform the user, and set the return value of its Response argument to
acDataErrContinue to suppress the system generated dialogue. The record will
be refreshed and user 1’s changes dropped.

I don’t know of any easy way to copy user 1’s data to the clipboard. You’d
probably need to simulate this by writing the values in the form’s controls
to a module level array, which you could then read from. As you want to
prevent another user’s changes being overwritten, however, there would not
seem to be any need to copy user 1’s changes. Getz, Litwin and Gunderloy
comment in the ADH that this is only a good choice for *the sophisticated
user*, but requires too much understanding for the naïve user, so is maybe
best omitted in any case.

You might also want to customize the handling of Error number 7878, which is
raised if another user has changed the record when user 1 first attempts to
edit it since starting to view it. As no changes have been made by user 1
simply pop up a message and return Response = acDataErrContinue to refresh
the record with user 2’s changes.

Ken Sheridan
Stafford, England
 
Ken said:
riyaz:

If one user attempts to save a record which has been changed by another user
since user 1 began editing it Error number 7787 is raised so you should
handle this in the form's Error event procedure, popping up a custom message
box to inform the user, and set the return value of its Response argument to
acDataErrContinue to suppress the system generated dialogue. The record will
be refreshed and user 1's changes dropped.
Click to expand...

ken, this is my life saving response :) thanx mate.

also, any chance of doin this in a module level function called from
the OnError property like "=formErrorHandler([Form], ...)". is it
possible to get access to the Response argument?

i can drop the Clipboard option, so that does not matter.
You might also want to customize the handling of Error number 7878, which is
raised if another user has changed the record when user 1 first attempts to
edit it since starting to view it. As no changes have been made by user 1
simply pop up a message and return Response = acDataErrContinue to refresh
the record with user 2's changes.
Click to expand...

ken in my testing i have gone through with this but no error was
raised. eg: 2 users open same form/dataset. user1 saves changes. when
user2 starts making changes NO error is raised. only when user1 tries
to save it, the first error (as per your reply, err number 7787)

how come i'm not seeing it?

thanx alot.
riyaz
 
clarification::

ken in my testing i have gone through with this but no error was
raised. eg: 2 users open same form/dataset. user1 saves changes. when
user2 starts making changes NO error is raised. only when user2 (not
user1) tries
to save it, the first error (as per your reply, err number 7787)
 
riyaz:

You can go beyond handling it at module level in fact and handle it
globally. Just add a function along these lines to a standard module:

Function HandleWriteErrors(errNum As Integer) As Integer

Dim strMessage As String

Select Case errNum
Case 7787
strMessage = "Another user has edited this record " & _
"since you began to edit it." & vbNewLine & vbNewLine & _
"Your changes will not be saved."
HandleWriteErrors = acDataErrContinue
MsgBox strMessage, vbInformation, "Write Conflict"
Case 7878
strMessage = "Another user has edited this recoprd " & _
"since you began to view it." & vbNewLine & vbNewLine & _
"Your changes will not be saved."
HandleWriteErrors = acDataErrContinue
Case Else
HandleWriteErrors = acDataErrDisplay
End Select

End Function

And in the Error event procedure of any bound form call it with:

Response = HandleWriteErrors(DataErr)

I doubt you can call it directly from the property sheet, however.

I think the 7878 error will only be raised if one user begins to edit a
record which has not yet been refreshed in the form. You’ll probably find
that when you test it in your form the record is being refreshed with the
other users changes before you begin to edit it. Its probably worth leaving
in nevertheless to handle any situations where that might not be the case.

Ken Sheridan
Stafford, England
 
Ken said:
I doubt you can call it directly from the property sheet, however.
Click to expand...

thanx ken. the above was waht i was afraid of :(

i have been cleaning up the code for .MDB project; by calling just
functions from the property sheet. file size had reduced and it had
become *much* more manageable. i had even removed the form module!

one thing i had not been able to do was to use the NotInList event for
comboboxes. as you know, it also has a custom argument (Response) which
needs to be set as per requirement (ie, ignore/display system message
....)

but it seems more and more that these functions can ONLY be used from
with the form module only :(

thankfully, DoCmd.CancelEvent took care of all the BeforeUpdate events
argument (Cancel).

i guess its back to square 1 huh.

thanx for everything ken
riyaz
 
Back
Top