Transaction is affected by a recordset

Sorry to my poor English.
I'm a program using Visuual Basic. I write the following code , as you see, I want to delete some data in a transaction , in the transaction I execute "delete ..." first , and then open a recordset. At the end ,I commit the transaction . But , delete is no effect . When I instead the parameter "adLockOptimistic" with "adLockReadOnly" . the delete runs successful. Why ?

I notice that if I use parameter "adLockOptimistic" and the "sql" selects from two table or selects from a view , con.Execute "delete from xsfpex " runs with no effect .

I also notice that if I change rs.CursorLocation to adServerClient , the "delete" runs successful .

the source codes:
'------------------------------------------------
con.BeginTrans

Dim rs As New Recordset
rs.CursorLocation = adUseClient
con.Execute "delete from xsfpex "

Dim sql As String
sql = "select a.tablename from wise_table a, wise_table b where a.isleaf=1 and a.type_=3 and a.ptableid=b.id and b.tablename='xsfp'"
rs.open sql, con, adOpenStatic, adLockOptimistic, adCmdText

rs.MoveFirst

Do While Not rs.EOF
Debug.Print rs.Fields(0).Value
rs.MoveNext
Loop

rs.Close

Set rs = Nothing

con.CommitTrans

'------------------------------------------------

　　　　　　　　junzeng(at)netease(dot)com
　　　　　　　　　　2003-02-25