Transaction Processing in a Modular Environment

[Terry VanDamme]

Hello,

I am attempting to write an application where I have code segregation where code dealing with the form is located in the form which relies on a class module (ie clsOrders) and all code that specifically deals with database connection, manipulation, etc is resident in it's own standard module.

I am using ADO and Access 2003 at the moment. I am wondering what the best method is to implement transaction processing.

I have a method for executing the sql, one for opening the database, one for closing it, one for supplying the required SQL, etc in the database module.

There is a function in the clsOrders module - say InsertOrder that calls the function in the db module to get the sql string and then calls a function in the db module (ProcessOrderIn sertion) that in turn calls the OpenDBConnectio n function and then the ExecuteQuery Function using the current sqlString, then calls CloseDBConnecti on and then exits back to the InsertOrder function in the class module which then gets the string for the next transaction and then calls ProcessOrderIns ertion in the db module again, etc until all queries in the current group are processed.


So using the above setup I am wondering if it is possible to use the following code (simplified) in the Calling Function in the Class module?

 
On Error GoTo Err_Handler
 
  cn.BeginTrans
 
  Call Functions in other module until
  all sql statements are done
 
  cn.CommitTrans
 
Err_Handler:
  cn.RollbackTrans
 
 

An issue that I see or am not sure of how to readily deal with or have questions about is:

Where the functions processing the data are in another module and bearing that in mind, will the error handling code properly detect an error that would happen in the ExecuteQuery Function that is two function levels below the calling one and in a different module?

Also, the code creating the ADO connection (cn) is also located in the database module which would suggest that the transaction handling code should also be in the same module.

The ProcessOrderIns ertion only processes one query at a time prior to passing control back. This is like this as it is a generic function used by all other sections of the application and there is some times only one statement run but other times there would be multiple statements.

It's easy enough to call the DBConnect and Disconnect functions seperately but I guess again the main question is how to implement the error detection mechanism to decide whether to commit or rollback.

Any insight would be much appreciated.

Thank you,
Terry Van de Velde

PS. My last name is not VanDamme, however the name field on the registration form did not have enough room for my real name.

[Terry VanDamme]

Thanks for any who would reply.

I have located this post which I think answers my question:

http://bytes.com/topic/access/answer...ction-question

If there is anything that you feel would help me further, I'd be happy to hear from you.

Regards,
Terry Van de Velde