ADO Transactions with SQLSERVER(??)

[amolt]

Hi All,
I designed an client/server application which updates and reads data from and to server.

I am having a big problem in transactions, The Code is :

Provider Used to open connection with SQL Server 2005 : sqloledb

Private Function UpdateRemoteDB()
On Error GoTo ErrHnd
    Dim Con as New ADODB.Connection
    Dim rsSQL as New ADODB.Recordset

    Con.CursorLocation = adUseServer 
    Con.ConnectionString = SQLCONNECTIONSTRING
    Con.Open

    'Later update each record from local database to server
    
    Do While not rsLocalRecord.EOF = True
             
             Con.BeginTrans

             rsSQL.CursorLocation = adUseClient
             rsSQL.Open "SELECT * FROM MyTable WHERE ID = '11'", _
                             Con, adOpenKeyset, adLockOptimistic

             rsSQL("Field1") = rsLocalRecord("Field1")
             rsSQL("Field2") = rsLocalRecord("Field2")
             rsSQL("Field3") = rsLocalRecord("Field3")
             :
             :
             rsSQL.Update
             rsSQL.Close

             'I need to do some other processing here. 
             If OtherFunction() = False then
                  GoTo ErrHnd
             End If

             Con.CommitTrans
    Loop
    Exit Function
ErrHnd:
    Con.RollbackTrans
End Function

On other Client End I just displayed all the updated records from SQL Server
with "SELECT * FROM MyTable" and show them to grid.

The main problem above is while my first client is in transaction till the CommitTrans is not executed my other client cannot displayed the results for query.

What should I can do so that the Concurrent Updates are possible!!!!!!

I read about Connection Properties in MSDN so tried likewise
Con.Mode = adModeShareDenyNone
Con.IsolationLevel = adXactReadCommitted
But nothing really happens !!!!


Thanks !!!!!!!!