R
robert d via AccessMonster.com
I need to implement something to prevent write conflicts when more than one
user tries to update a record on my unbound forms. Also, if two or more
users have the same record open and one edits it, other users may not be
aware this happened and processing needs to inform them if they then try to
edit the same record.
Again, my forms are unbound and I'm using DAO. So below is an example of
how I'm considering handling this. There is a custom global error handler
which is referenced, but the code for that is not shown. Also, ConcurrencyID
is the field that will be used to see if the record has been changed by User
2 after User 1 has loaded the record but prior to User 1 trying to edit and
update the record. When the record is updated, ConcurrencyID is incremented
by 1. It is then rechecked prior to updating to make sure that it has not
changed since the record was retrieved.
Sub EditRecord()
On Error GoTo Err_Ctrl
Dim wsEDIT As DAO.Workspace
Dim dbEDIT As DAO.Database
Dim rsEDIT As DAO.Recordset
Dim lngConcurrencyID As Long
Dim strSQLEDIT As String
strSQLEDIT = "SELECT Field1, ConcurrencyID FROM [TABLEA]"
Set wsEDIT = DBEngine.Workspaces(0)
Set dbEDIT = wsEDIT.OpenDatabase(CurrentProject.FullName)
Set rsEDIT = dbEDIT.OpenRecordset(strSQLEDIT, dbOpenDynaset, dbSeeChanges)
'Begin the Transaction Loop
wsEDIT.BeginTrans
'Requery the record to see if the ConcurrencyID has changed. Note
lngConcurrencyID is a variable
'defined globally for the form and was set when the record was first
retrieved and displayed.
rsEDIT.Requery
If lngConcurrencyID = rsEDIT("ConcurrencyID") Then
'Record has not been changed by any other users up to this point
rsEDIT.Edit
rsEDIT("Field1) = "Hello World"
rsEDIT("ConcurrencyID") = lngConcurrencyID + 1
rst.UPDATE
'Commit the Transaction; Everything went as Planned
wsEDIT.CommitTrans
Else
MsgBox "The record has been changed since you retrieved it. Update you
requested will not be executed.", vbOKOnly + vbExclamation
wsEDIT.Rollback
End If
Exit_Sub:
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Sub
Err_Ctrl:
wsEDIT.Rollback
errMsgStr = ""
ctrlfnctnm = "EditRecord"
Call NamedForm_err(Err.Number, Err.Description, Err.Source, ctrlfnctnm,
errMsgStr)
Resume Exit_Sub
End Sub
user tries to update a record on my unbound forms. Also, if two or more
users have the same record open and one edits it, other users may not be
aware this happened and processing needs to inform them if they then try to
edit the same record.
Again, my forms are unbound and I'm using DAO. So below is an example of
how I'm considering handling this. There is a custom global error handler
which is referenced, but the code for that is not shown. Also, ConcurrencyID
is the field that will be used to see if the record has been changed by User
2 after User 1 has loaded the record but prior to User 1 trying to edit and
update the record. When the record is updated, ConcurrencyID is incremented
by 1. It is then rechecked prior to updating to make sure that it has not
changed since the record was retrieved.
Sub EditRecord()
On Error GoTo Err_Ctrl
Dim wsEDIT As DAO.Workspace
Dim dbEDIT As DAO.Database
Dim rsEDIT As DAO.Recordset
Dim lngConcurrencyID As Long
Dim strSQLEDIT As String
strSQLEDIT = "SELECT Field1, ConcurrencyID FROM [TABLEA]"
Set wsEDIT = DBEngine.Workspaces(0)
Set dbEDIT = wsEDIT.OpenDatabase(CurrentProject.FullName)
Set rsEDIT = dbEDIT.OpenRecordset(strSQLEDIT, dbOpenDynaset, dbSeeChanges)
'Begin the Transaction Loop
wsEDIT.BeginTrans
'Requery the record to see if the ConcurrencyID has changed. Note
lngConcurrencyID is a variable
'defined globally for the form and was set when the record was first
retrieved and displayed.
rsEDIT.Requery
If lngConcurrencyID = rsEDIT("ConcurrencyID") Then
'Record has not been changed by any other users up to this point
rsEDIT.Edit
rsEDIT("Field1) = "Hello World"
rsEDIT("ConcurrencyID") = lngConcurrencyID + 1
rst.UPDATE
'Commit the Transaction; Everything went as Planned
wsEDIT.CommitTrans
Else
MsgBox "The record has been changed since you retrieved it. Update you
requested will not be executed.", vbOKOnly + vbExclamation
wsEDIT.Rollback
End If
Exit_Sub:
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Sub
Err_Ctrl:
wsEDIT.Rollback
errMsgStr = ""
ctrlfnctnm = "EditRecord"
Call NamedForm_err(Err.Number, Err.Description, Err.Source, ctrlfnctnm,
errMsgStr)
Resume Exit_Sub
End Sub