We set a rule, if we're going to open a transaction we do it in a Sproc (we using SQL Server) and we close it in the same sproc. That way we can be sure that transactions are never left floating (unless the tea lady kicks the plug out of the server, in which case we've got bigger problems).
I don't necessarily want to hijack the thread on this topic, but sometimes a transaction in the SP isn't enough... Normally I'd agree with your assessment Funky, but sometimes you have no choice but to initiate the transaction on the client and utilize a distributed transaction across multiple servers - a potential mess, yes, but that's where kevlar error handling comes in... it isn't bulletproof, but it certanly slows down the problems when they do arise.

OK, back on topic...

In any case, since we are talking about Access here, I agree... try not to use a transaction if you can help it. One thing though, there seems to be a lot of selecting and inserting into the same table going on... what does this process do? There might be a better way to perform the same action that isn't so.... heavy on the selects and inserts. and looping.

-tg