Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Posts
    28

    Unanswered: Does JET has this feature ?

    Hello,

    Does JET has the ability to lock the records and deny other processes to read them when I issue just single sql SELECT statement ?

    TIA

  2. #2
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91

    Re: Does JET has this feature ?

    Originally posted by suntisuk
    Hello,

    Does JET has the ability to lock the records and deny other processes to read them when I issue just single sql SELECT statement ?

    TIA

    What do you mean: "I issue a just single SELECT statement ?"
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  3. #3
    Join Date
    Nov 2003
    Posts
    28

    Re: Does JET has this feature ?

    Originally posted by Mixirica
    What do you mean: "I issue a just single SELECT statement ?"
    Hello, Sorry for my English language. I mean if I issue "SELECT * from TABLE_A where TABLE_A.Key = 100" to JET, Can JET read and immediately lock the result record.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why do you need it locked? Are you displaying the information on the form? If so, then set then you need to set the "recordset type" to "snapshot" on the data tab of the form (or through vb if that is your preferance).

  5. #5
    Join Date
    Nov 2003
    Posts
    28
    Hello Teddy,

    I have problem when my procedure run simultanously. This procedure update the same record. I found that if 2 processes can get the same value, suppose 10. Then, first process add 1 and update record. Now in database is 11. Next, second process do the same thing by add 1 and update record. Result is also 11 which is incorrect. I think only solution to protect this kind of problem is JET have to support a SELECT statement that read and lock at the same time (not lock at the time of update).

    You can see my earlier post in "Help please : Does transaction protect data ?" on 11-05-03. One thing I wonder is eventhough JET can not read+lock. But at the time of wkRS.Update of second process, JET should check onhand value (that is 10) is not the same as in database (already changed to 11). It should raise the error like "Data have been changed by other user since you read it". I don't know why it does not generate that error. If it does, User can retry again.

    Have any solution ?

  6. #6
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I would be intereseted in an answer to this too. It would be very usefull to be able to lock a record at the exact point that the record has been SELECTed by a query.

  7. #7
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    Originally posted by bcass
    I would be intereseted in an answer to this too. It would be very usefull to be able to lock a record at the exact point that the record has been SELECTed by a query.
    Guys, we may do this using RecordSets. The propertie LockType will control the access to the records:

    Dim rs As ADODB.Recordset
    Dim sql As String

    Set rs = New Recordset


    With rs
    .CursorType = adOpenForwardOnly

    .LockType = adLockPessimistic ' this is THE KEY


    .Source = "SELECT Myfields FROM myTable"
    .Open , CurrentProject.Connection


    ... Your code

    .close
    end with

    set rs = nothing

    Now, you should look to Access´s Help in order to know other choices of lock type.
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  8. #8
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Do you have a DAO example of that code? Also, how foolproof is it? Is there *any* possibility that 2 two people could lock the same record?

  9. #9
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    Originally posted by bcass
    Do you have a DAO example of that code? Also, how foolproof is it? Is there *any* possibility that 2 two people could lock the same record?
    As I say in the footer of my messages, I use DAO 3.6, so I only have this example. And, as far as I know, it works very well.
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  10. #10
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    OK, thanks, I'll give it a try.

  11. #11
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Hello again. I have been trying out your code to try and lock a single record, but without success. Here is what I have:

    Code:
        Dim rs As ADODB.Recordset
        Dim sql As String
    
        Set rs = New ADODB.Recordset
    
        With rs
            .CursorType = adOpenForwardOnly
            .LockType = adLockPessimistic
    
            .Source = "SELECT * FROM [T_Employee] WHERE [Pay Number] = '" & txtPayNumber & "';"
            .Open , CurrentProject.Connection
    
            .Close
            End With
    
            Set rs = Nothing
    When inspecting the table once the record is open, I notice that the record selector does not show a locked indicator. Also, other users are still able to make amendments to the record in the tables file. Where am I going wrong? Once I have a specified record open I need it to remain locked for other users until I requery my form and go to another record.
    Last edited by bcass; 11-10-03 at 14:30.

  12. #12
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    Originally posted by bcass
    Hello again. I have been trying out your code to try and lock a single record, but without success. Here is what I have:

    Code:
        Dim rs As ADODB.Recordset
        Dim sql As String
    
        Set rs = New ADODB.Recordset
    
        With rs
            .CursorType = adOpenForwardOnly
            .LockType = adLockPessimistic
    
            .Source = "SELECT * FROM [T_Employee] WHERE [Pay Number] = '" & txtPayNumber & "';"
            .Open , CurrentProject.Connection
    
            .Close
            End With
    
            Set rs = Nothing
    When inspecting the table once the record is open, I notice that the record selector does not show a locked indicator. Also, other users are still able to make amendments to the record in the tables file. Where am I going wrong? Once I have a specified record open I need it to remain locked for other users until I requery my form and go to another record.
    Let´s try another way...

    Go to Tools/Options/Advanced (At least, in portuguese this is the right sequence !)

    Now, u have two boxes:

    1-Standard Opening mode (I guess), with 3 options. Check the Exclusive mode

    2- Standard record protection: Check Edited Record.


    Sorry if the options aren´t in the rigth sequence.

    Good luck
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  13. #13
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    The code is okay what you should also look for is when you are locking the record.

    Rule of thumb is whenever you have mutiple porcessing accessing sme record, you lock the record just when you want to update it. so don't create the recordset right in the begining of the process and then update it later on, but create a recordset, update the value and release the record instantly.

    That way you'll never have this problem.

    I haven't tested your code but thats how you lock records.
    but try using adlockoptimistic property for record locking. The constant locks the record only when you issue the update command.



    Originally posted by bcass
    Hello again. I have been trying out your code to try and lock a single record, but without success. Here is what I have:

    Code:
        Dim rs As ADODB.Recordset
        Dim sql As String
    
        Set rs = New ADODB.Recordset
    
        With rs
            .CursorType = adOpenForwardOnly
            .LockType = adLockPessimistic
    
            .Source = "SELECT * FROM [T_Employee] WHERE [Pay Number] = '" & txtPayNumber & "';"
            .Open , CurrentProject.Connection
    
            .Close
            End With
    
            Set rs = Nothing
    When inspecting the table once the record is open, I notice that the record selector does not show a locked indicator. Also, other users are still able to make amendments to the record in the tables file. Where am I going wrong? Once I have a specified record open I need it to remain locked for other users until I requery my form and go to another record.

  14. #14
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Originally posted by Mixirica
    Let´s try another way...

    Go to Tools/Options/Advanced (At least, in portuguese this is the right sequence !)

    Now, u have two boxes:

    1-Standard Opening mode (I guess), with 3 options. Check the Exclusive mode

    2- Standard record protection: Check Edited Record.
    My options are already set up like that (except I need to open in Shared mode, since this database is shared between mutiple users).

    Here is exactly what I want to achieve: User locks a record that they are about to view/edit - for the whole time that the user is viewing/editing this 1 record, I need it to be locked so no one else can view/edit it (or at least lock it in a way where I can read the locked status so I can determin if the record is locked if another user tries to open it - so I can prompt the 2nd user that the 1st user has the particular record open/locked.)

    I understand how to lock the record (from your code example) but how do I then make that record the record source for the form of the user who has locked the record (ie - the only person who can currently view it)? If I use a query then the record doesn't appear locked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •