sorry, my english is poor, what do u mean " tea lady kicks the plug out"?
It was a joke. Don't worry about it. All it really meant was "If the computer crashes for some reason".

i get ur idea about using begintrans in a stored procedure.. What do you suggest in case i just can use VB code to begin a transaction in Access database?
I don't really use acces so I'm not sure but I think you can create "Queries" in Access that can contain several statements. That could be wrong though.

My best suggestion would be: Don't use Access. It's not really meant for multi user or complex systems. Instead I would suggest using SQLServer becaue then you can use Sprocs. If cost is an issue then use SQLServer Express which is free. To be honest, I can't think of any reason why anyone would continue to use Access when SQLServer Express is cheaper (free) and has more features.

The only other thing you could do would be to try and achieve everything you want with a single sql statement which is Techgnome was aiming at. For Example, this:-
Code:
str_SQL = " select ... from TableC ...where ..."

If f_OpenSQLrs(aRs, str_SQL, adCmdText, gConnect_Access, adUseClient, adOpenKeyset) Then
      i = 0
      While Not aRs.EOF
            str_SQL = " insert into TableD ..."
            gConnect_Access.Execute str_SQL

            aRs.MoveNext
      Wend
      Call fn_closeADOrs(aRs)
Else
      GoTo Err
End If
Could be rewritten as this:-
Code:
str_SQL = " Insert Into TableD (field1, field2,...) " & _
"select fieldA, fieldB ... from TableC ...where ..."
gConnect_Access.Execute str_SQL
There's no need for a transaction. On the other hand you may have some operations that just can't be reduced to a single statement.