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

    Unanswered: Help please : Does transaction protect data ?

    Sorry to post this message again. I faced this problem for a week and still not found any solution.

    Hi, I use AccessXP + ADO to access data in .MDB file. I write code to update value in my table which always contains only 1 record. In multiuser environment, this code may be run simultenously. Problem is during the first user is updating value but haven't committrans , the second one can also read the same record to be updated. This will lead into inconsistency value problem. Although Access show an error message that record has been changed by other user before second user save record. But that is not what I expect because this error always be displayed when this code used simulteneously and extensively.

    Question are:

    1) To solve this problem, if Access can exclusive lock the updating record in transaction, Everything will work properly. Can it do that ?

    2) I haved checked .IsolationLevel property of connection object, It is adXactReadCommitted (=4096). This value should do what I want BUT it does not. Have any idea ?

    3) Do I have to setup or do something for multiuser environment ?


    This is my code :

    Private Sub UpdateValue()
    Dim wkCN As New ADODB.Connection
    Dim wkRS As New ADODB.Recordset
    Dim wkSQL As String

    wkSQL = "select * from M007 where M007_Lot_Key = 1 "
    wkCN.CursorLocation = adUseClient
    wkCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\DB\Database.MDB"
    wkCN.BeginTrans
    wkRS.Open wkSQL, wkCN, adOpenKeyset, adLockPessimistic, adCmdText
    wkRS.MoveFirst
    wkRS!M007_Lot_NextNo = wkRS!M007_Lot_NextNo + 1
    ...
    ...
    ...
    wkRS.Update
    wkCN.CommitTrans
    wkRS.Close: Set wkRS = Nothing
    wkCN.Close: Set wkCN = Nothing
    End Sub

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: Help please : Does transaction protect data ?

    How did you check that a noncommited value can be read by another user?

    Instead of opening a Recordset and then updating it, can you build an UPDATE statement and use the Execute method of the Connection object?

    For example:

    Private Sub UpdateValue()

    Dim wkCN As ADODB.Connection
    Dim wkSQL As String
    Dim blnTransPending As Boolean
    '
    On Error GoTo SomethingWrong
    '
    Set wkCN = New ADODB.Connection
    wkCN.CursorLocation = adUseClient
    wkCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\DB\Database.MDB"
    wkSQL = "UPDATE M007 SET M007_Lot_NextNo = M007_Lot_NextNo+1,
    ... (other fields to be updated) ...
    WHERE M007_Lot_Key = 1"
    wkCN.BeginTrans
    blnTransPending = True
    wkCN.Execute wkSQL, adExecuteNoRecords + adCmdText
    wkCN.CommitTrans
    blnTransPending = False
    '
    OverAndOut:
    If Not (wkCN Is Nothing) Then
    If wkCN.State = adStateOpen Then
    wkCN.Close
    End If
    End If
    Set wkCN = Nothing
    Exit Sub
    '
    SomethingWrong:
    If blnTransPending Then
    wkCN.RollbackTrans
    End If
    '
    ' code to handle the error
    '
    Resume OverAndOut
    '
    End Sub

  3. #3
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15
    I feel the problem is your cursor location (adUseClient) which makes the entire recordset to be sent to the client machine.
    After updation the recordset will be sent back to server.
    It is compared with the actual values in the database.
    If changed shows u the message like "updated by some other user".

    Try using adUseServer and the server should take care of any updations automatically without popping up messages.

  4. #4
    Join Date
    Nov 2003
    Posts
    28

    Re: Help please : Does transaction protect data ?

    Originally posted by mashinovodja
    How did you check that a noncommited value can be read by another user?
    If 2 machine run this procedure at the same time. They get the same value of M007_Lot_NextNo


    Originally posted by mashinovodja
    Instead of opening a Recordset and then updating it, can you build an UPDATE statement and use the Execute method of the Connection object?
    No, can not. I have to use M007_Lot_NextNo before it will be updated.

  5. #5
    Join Date
    Nov 2003
    Posts
    28
    Originally posted by venkyt
    I feel the problem is your cursor location (adUseClient) which makes the entire recordset to be sent to the client machine.
    After updation the recordset will be sent back to server.
    It is compared with the actual values in the database.
    If changed shows u the message like "updated by some other user".

    Try using adUseServer and the server should take care of any updations automatically without popping up messages.
    I have tried to use adUseServer, It does not work also. 2 machine still can run this procedure at the same time and get the same value. I don't know why JET not take care it ???

Posting Permissions

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