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 read the same record. What I want is unless transaction is committed, no one can read that record.

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 ?

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"
wkRS.Open wkSQL, wkCN, adOpenKeyset, adLockPessimistic, adCmdText
wkRS!M007_Lot_NextNo = wkRS!M007_Lot_NextNo + 1
wkRS.Close: Set wkRS = Nothing
wkCN.Close: Set wkCN = Nothing
End Sub