Ernest,
>> The problem is how do I know if the entire transaction succeeded or failed.
The stored procedure will have to return a value signaling the status of the process. There are three direct ways to get a value from a stored procedure. The first would be to have the stored procedure return a result set. The second would be use use the RETURN command to return an integer value. Microsoft has reserved the value 0 through -99 but you can still use this for their original intent (BTW, by convention, a 0 means success).
The third option is to use an output parameter - similiar to what you're currently trying to do.
Now, not being that familiar with the DataEnvironment, I can't help with it. But as for straight ADO, I think to catch OUTPUT parameters and RETURN values, you have to use a command object and declare parameters. To catch the RETURN value, create a parameter of type adParamReturnValue and I think it has to be the last parameter.
-Mike