Title: Transactions with ADO
Question: How to set up a transaction with ado ?
Answer:
When you post,update or delete data you want it to be done the right way or it must not be done at all. This can be done with transactions.
The code below is an example on how to setup your first transaction.
procedure TForm1.Button1Click(Sender: TObject);
var
ErrorOccured : boolean;
begin
ErrorOccured := False; // Initialize boolean;
with DataModule1 do
begin
try
{DBConnection : TADOConnection }
DBConnection.BeginTrans; // Begin transaction ...
// Do your database magic here ....
DBConnection.CommitTrans; // Commit data ....
except
on E:Exception do
begin
DBConnection.RollbackTrans; // on failure rollback changes.
ErrorOccured := True; // set boolean to true cause error has occured.
MessageDlg('Transaction failed !' + #13 + 'ERROR: ' + e.Message,
mtError,[mbOk],0); // Post error on screen for user ...
end;
end; //try
if ErrorOccured = False then
begin
{ Here you can do some extra things when an error has occured. For
example not close the form or any other special needs }
end;
end;
end;
This transaction method will work fine with Access and ADO with "OLE DB for ODBC" as provider. But when you want to make nested transactions, you need to change the provider otherwise it will give errors.
Nested Transactions :
For nested transactions you need to select a different provider to get it to work.Choose the "Jet OLE DB" provider and then nested transactions will work fine.
I hope this article can contribute to the community.