Hi, I try to use ADODB.recordset. I have selected the right document from the table, with pessimistic locking, but it doesnt seem to fix my problem - other user can do the same, without any problem, no locking occuring, seems to realy lock the recordset just during .update method, doesnt need that i need to lock it before any update occure, user can take minutes to edit the document. MySQL locking seems to work the same, useful for transactions whitch are executed.
More info about the process:
All fields on edit form are unbound.
When user open form, unbound fields are filled with document data using VBA from mySQL.
(This transaction will be quick and end fast, even if i lock, lock on ms access recordset work just when update methot is called, so when the user takes 5 minutes to edit the document, how to make sure, no one can edit the same document?)
User can now edit the fields.
Save by click on button save.
Values are then converted to MySQL string, passed to MySQL procedure
User One opened form to edit document, how to get other users know, that that document is being edited?
User Two try to open form to edit the same document, how get info that someone is editing the document?
Code:
Private Sub cmdExecute_Click()
'Inicializace databáze
Dim rec As ADODB.Recordset
Dim Base As clsDatabase
Set Base = Factory.CreateDatabase(MySQL())
'Here i want to release the lock
CheckInputType
With Me
Select Case InputType
Case "create"
Set rec = Base.rec("CALL procedure_newdocument('" & .txtDocumentID & "', '" & _
.cboDocumentType & "'," & _
.txtRevision & ", '" & _
.cboOwner & "'," & _
CLng(.chkDigital And 1) & ", '" & _
Format(Now(), "yy-mm-dd hh:mm:ss") & "', '" & _
.txtTitle & "', '" & _
.txtChangeDesc & "', '" & _
PcUserName & "');", Procedure:=True)
Case "change"
Select Case CInt(Left(.txtRevisionStatus.VALUE, 1))
Case 2
Set rec = Base.rec("CALL procedure_updatedocument('" & .txtDocumentID & "', '" & _
.cboDocumentType & "'," & _
.txtRevision & ", '" & _
.cboOwner & "', '" & _
Format(Now(), "yy-mm-dd hh:mm:ss") & "', '" & _
.txtTitle & "', '" & _
.txtChangeDesc & "', '" & _
PcUserName & "');", Procedure:=True)
Case 3
Set rec = Base.rec("CALL procedure_revisiondocument('" & .txtDocumentID & "', '" & _
.cboDocumentType & "'," & _
(CLng(.txtRevision) + 1) & ", '" & _
.cboOwner & "', '" & _
Format(Now(), "yy-mm-dd hh:mm:ss") & "', '" & _
.txtTitle & "', '" & _
.txtChangeDesc & "', '" & _
PcUserName & "');", Procedure:=True)
End Select
End Select
End With
CloseForm ("DocumentProperties")
End Sub
Private Sub Form_Load()
Dim arrarg() As String
'Inicializace databáze
Dim Base As clsDatabase
Set Base = Factory.CreateDatabase(MySQL())
'Vyplnění cbo pomocí dao
Set Me.cboDocumentType.Recordset = Base.RecDAO("Select * From tbl_document_types")
Set Me.cboOwner.Recordset = Base.RecDAO("Select * From tbl_users")
If InStr(Me.OpenArgs, ";") > 0 Then
arrarg() = Split(Me.OpenArgs, ";")
'Kontrola a rozdělení dokumentu na type/id
'Here i want to lock document arrarg(1) and check if it is locked
If Document.Check(arrarg(1)) Then
arrarg() = Split(arrarg(1), "/")
Else
CloseForm ("DocumentProperties")
Exit Sub
End If
InputType = "change"
SetChangeValues arrarg(0), arrarg(1)
Else
InputType = "create"
SetDefaultValues
End If
End Sub