Results 1 to 7 of 7
  1. #1
    projedenpost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    3

    Question MS Access form locking

    Hi. I have ms access 2016 and use mysql backend. I am not sure about function i want to implement, if it is good or bad idea.




    Database will be used for document managment. Maximum about 1-2users will edit database at the same time.


    I have a form for editing document. All updates and insterts are done using mysql side procedures/routines, so there is no open recordset for write, all are readonly. Multiple users can edit one document at the same time, last one will win. I dont want that, i want to block the second user from opening form to edit the same document.


    I am not sure if it is good idea, but a would like to create table on mysql server, to hold information about who block what document. Not a great idea, because i would have to make sure to delete the record when form is closed. What do you thing? Do you need more information?


    I am inspired by SAP, which does tell who block what record, but i know the implementation is very diffrent, solid and all right.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would ask this question on a mySQL forum. Access has recordlocking capabilities, I expect mySQL has too.

    With Access you would could such as in this link http://www.utteraccess.com/forum/che...-t1834320.html

  3. #3
    projedenpost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    3
    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

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In SharePoint you can "check out" a file. When checked out it is not accessible to anyone else. I have used Automation from Access vba to check SP files in and out. However, should this fail you are no better off that if you simply checked a field in a table to prevent anyone else from doing more than accessing a record as read only. As long as the form closes, the flag should be removed if coded correctly, but there can be abnormal shut downs that could prevent this. In that case, I'd provide an admin level override for when admin confirms that the file is not being worked on. In that case just remove the flag. You probably want to have the logged on user name in the record; might as well use that as the flag.

    Hope your db is split and every user has their own front end, otherwise you are on thin ice here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What does this mean in terms of your editing?

    All updates and insterts are done using mysql side procedures/routines, so there is no open recordset for write, all are readonly
    .

  6. #6
    projedenpost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    3

    Lightbulb

    Quote Originally Posted by orange View Post
    What does this mean in terms of your editing?

    .
    Recordsets are not used for editing. But I find sulution to my problem here: https://stackoverflow.com/questions/...ocking-via-php,

    So I will just create locking table in mysql server, like i wanted .

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    OK Glad you have an answer. I couldn't follow how you were editing when the recordset was READOnly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-18-2018, 09:02 PM
  2. Replies: 1
    Last Post: 12-24-2014, 03:49 PM
  3. Locking down Access
    By snipe in forum Access
    Replies: 6
    Last Post: 05-22-2014, 02:43 PM
  4. Locking fields in Access form
    By sk88 in forum Access
    Replies: 10
    Last Post: 06-04-2012, 01:40 PM
  5. Access Locking other users out
    By David in forum Access
    Replies: 1
    Last Post: 07-02-2009, 09:26 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums