Transactions on SQL Sever

[Microsoft]

Access 2000 VBA

I have a problem with transactions which I've isolated
into a simpler code like this

Sub pruebatransacc()
Dim r As Recordset
DBEngine.beginTrans
Set r = CurrentDb.OpenRecordset("select * from enti")
r.addNew
r("nombre_enti") = "prueba"
r.Update
Set r = CurrentDb.OpenRecordset("select * from enti")
DBEngine.rollback
End Sub

If 'enti' is a linked table located on Oracle, it works
fine. But if it's Sql Server, the code hangs on the second
openrecordset, until the odbc timeout is reached.

Searching in Microsoft info, i've found that JET issues a
connection for each query, so the server locks the second
one, thinking that the first one has the transaction
pending. But, if it's a jet problem, why it works on
Oracle?

I'm very confused with this, can someone help me?

mmm this code is the simpler example, the real code is
huge. Please, don't say "make it passthrough". It's not
two queries. It's a beast. There are a lot of sql's inside
transactions, relying on the rollback feature...

Thanks anyway. Poor english, you've noticed, eh ;-)

 

[Van T. Dinh]

Try closing the first Recordset r (and a DoEvents) before opening the second
Recordset r.

 

[Microsoft]

$%$%$$%!!!!

Shit. It was that!!

Thanks a huge lot