Transactions VB6 + MySQL Database with ADODB.Connection

This concept may seem familiar and old-fashioned enough for us. But I feel this discussion is still important enough to write down here, especially for us who go into the database programming. For those who are well aware of this discussion, can be refreshing to just knowledge alone. Hopefully useful .
If you hear the word transaction, which occurred in our minds is definitely something the exchange process from one side to the other party. Similarly in the database, the transaction is a process of exchanging information / data from and to the database. Maybe it just feels normal, stay for the function:
Insert into namaTable values (‘data1’,’data2’…dst…)
finished ... but it will be very important


if we manage the data contain important information and requires accuracy of data such as sales data for e commerce, e banking, etc.. And the transaction process which consists of several stages.
OK .. rather than lengthy and tedious, I just give an example of case:
Andy went to the ABC Bank for the deposit transaction / savings. After the teller handed over, the teller will record these transactions as savings -> Deposit on his computer. The process actually consists of several stages of recording a transaction that could be described as follows:

1. recording and making new transaction number.
   
2. cash account debit process.
   
3. savings account credit process.
   
   Remember that in accounting, every transaction is a transfer of money from one account to another. In this case, your money will go to cash the bank (ie, increase cash flow for the account) and at the same time increase the bank's debt to you (ie, increase the credit in savings account). For each transaction, the amount of credit must be equal to the amount of flow so that the total 0 (balance).
   If the code is written as follows:
   

   Dim cKoneksi as new ADODB.Connection
   Private sub Form_Load
   cKoneksi.Open “Database Connection String”
   End Sub
   Private Sub cmdSimpan_Click
   cKoneksi.execute(“Insert INTO tblTransaksi(tgl,ket,teller_id) VALUES (“& format(now,”dd/mm/yyyy”) &”,”Teller1”,”& getMaxId &”)”)
   
   cKoneksi.execute("INSERT INTO tblKas (trans_id,jumlah) VALUES (“& getMaxId &”,jumlah))
   
   cKoneksi.execute("INSERT INTO tblTabungan (trans_id,norek_nasabah,jumlah) VALUES(“& getMaxId &”,norek_nasabah,jumlah))
   
   msgbox (“Data Sukses Disimpan”)
   End Sub

   
   The process is not a problem, if all phases of a success and all the data can be stored in the database perfectly. But imagine if there are things that we do not want to happen in the middle of the process such as power failure, computer hangs / error, etc. .. full database it can result in a process that is not perfect. If the process does not succeed or succeeded all would not matter, but what if the process is a success while others do not?
   For example after STEP 1 is complete, suddenly there was a problem so that steps 2 and 3 are not executed. The tellers will also explain the confusion that already exists record transaction activity, but in the accounts record no change.
   Then, if a problem occurs after step 2 is complete and has not executed langkah3, as a result customers will be angry angry because cash in the bank account grew, but the record did not improve customer balances.
   The things that we should anticipate as a database programmer, because it is impossible to check the process one by one. Especially if the process is composed of very complex data.
   For that in VB 6 is there a function to overcome this class contained in the ADODB.Connection. where if there is failure, then the system will rollback activities that have happened before, which means the process in question canceled altogether .. for more details I will show you through the following code:
   
   

   Dim cKoneksi as new ADODB.Connection
   Private sub Form_Load
   cKoneksi.Open “Database Connection String”
   End Sub
   Private Sub cmdSimpan_Click
   On error resume next --> akan mengabaikan semua error sampai proses selesai
   cKoneksi.BeginTrans --> membuka transaksi untuk koneksi tersebut
   cKoneksi.execute(“Insert INTO tblTransaksi(tgl,ket,teller_id) VALUES (“& format(now,”dd/mm/yyyy”) &”,”Teller1”,”& getMaxId &”)”)
   
   cKoneksi.execute("INSERT INTO tblKas (trans_id,jumlah) VALUES (“& getMaxId &”,jumlah))
   
   cKoneksi.execute("INSERT INTO tblTabungan (trans_id,norek_nasabah,jumlah) VALUES(“& getMaxId &”,norek_nasabah,jumlah))
   
   if error.number then --> jika ada error, maka rollback proses
   cKoneksi.RollbackTrans
   msgbox “Terjadi error. Proses terakhir telah dibatalkan”
   else --> jika tidak ada error, commit/jalankan proses
   cKoneksi.CommitTrans
   msgbox (“Data Sukses Disimpan”)
   end if
   End Sub

   
   presumably from the example code above was able to explain all the purposes of the transaction database. It is also very useful if we make the process repeated as insert data with loops.