ADO TSQL nested transaction...

[Klerman Gutierrez]

Hello,

I'm developing an Access 2000 data project, and I have a situation in this
code in that for each element selected in a list box, a SQL stored procedure
is called to perform updates in several tables.
What I would like to know is if an outer transaction opened in the VBA code
against the same ADO connection object that issues the calls to the stored
procedure will be able to rollback the transactions committed in the stored
procedure.

Thank you very much in advance,
Klerman

 

[Mary Chipman]

A far better approach would be to code the entire explicit transaction
in the stored procedure and simply call the stored procedure from ADO.
You can commit/rollback and send success/failure information via
output parameters in the stored procedure. See the "Explicit
Transactions" and related topics in SQL BooksOnline. Oh, and the
answer to your question is no. Once the transaction is committed in
the stored procedure, it can't be rolled back from ADO.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446