How to teach Microsoft Access in Oracle Transactions

Because my Article about how to teach Mircosoft Access in Oracle transactions seems to be very popular, I decided to post an English version of it. In future I will concentrate to write in English.

Microsoft Access is a widely used product in a number of companies in all industries. This is because the Microsoft Office package is widely used, and access solution 'so ubiquitous.
Unfortunately, this solution has its drawbacks. These are noticeable when man realizes an Access front end for a database connection. One of these drawbacks is the lack of ability to implement transactions or to use stored procedures and table functions.
About the Access interface alone this is not possible. While there is an SQL pass-through to execute DBMS-proprietary SQL queries, but these are often only possible static and need to be adjusted.
Visual Basic for Applications (VBA briefly) here can provide some relief. So it is possible, for example, assign the button controls of an Access form VBA macros (subs), and when you click it to run. Events like this onclick event can be implemented not only for the click of a button. Furthermore, there are over a dozen such events that can respond to the PLA. For example, an event is handled, when the form is opened, activated or closed.
It is also possible, through the Access Database API access to the workspace and start a transaction to later via a button abzusetzten a dynamic pass-through query and performed only after a commit or rollback. There are nessesary the following controls:

Type: Name:

Button But_Commit

Button But_Rollback

For Form_Open event all you need is line to start the transaction:

Private Sub Form_Open (Cancel As Integer)
 'Initialize a transaction
 DBEngine.BeginTrans
End Sub

We Also one must now end with the transaction commit in a OnClick event:

Private Sub But_Commit_Click ()
 'Commit transaction
 DBEngine.CommitTrans
End Sub

Or discard by Rollback

Private Sub But_RollBack_Click ()
 'Roll back the transaction
 DBEngine.Rollback
End Sub

Up here were all clear and simple one-liner, it is a bit more complicated now, insert the data into the database. Because the normal Access functions commit immediately, this may not even with the previous VBA statements are overridden. Instead, you must now use form fields that are associated with any of the attributes of a table. You only need to possess unique names:

Type: Name:

Button But_Insert

Textbox EdVorname

Textbox EdNachname

Textbox EdGeburt

Textbox EdMember

Note: Because this with a stored procedure is carried out, are passed two dates by a string in 'TT.MM.YYYY' (EdGeburt, EdMember). The code for the button to call the procedure that inserts the record:

 Private Sub But_Insert_Click()
 Dim ParamString As String
 Dim LSProc As QueryDef

 ' enable error handling
 On Error GoTo 0

 ' create parameters without parameter binding
 ParamString = "'" + Forms(thisForm).Controls("EdVorname").Value + "',"
 ParamString = ParamString + "'" + Forms(thisForm).Controls("EdNachname").Value + "',"
 ParamString = ParamString + "'" + Format(Forms(thisForm).Controls("EdGeburt").Value, "dd.mm.yyyy") + "',"
 ParamString = ParamString + "'" + Format(Forms(thisForm).Controls("EdMember").Value, "dd.mm.yyyy") + "'"

 ' create a querydef fpr global database var gDAODB
 Set LSProc = CurrentDb.CreateQueryDef("")

 'specify the connection manually
 LSProc.Connect = "ODBC;DSN=SLIGO_FH-TRIER"

 LSProc.SQL = "begin banking.insert_kunde(" + ParamString + "); end;"

 ' we don't expect any output
 LSProc.ReturnsRecords = False

 ' we ignore the timeout
 LSProc.ODBCTimeout = 0

 LSProc.Execute
End Sub

Now if you open the form, the transaction is initiated by the Form_Open event. The values ??are then inserted into the text boxes and click on the Insert button leads to the local Oracle session with an insert. If you open a second session and now looks at the database, so you will not see any changes.
Then you click the button Cut_Commit. A second search of the files on the second session shows that the stock has now changed - the transaction was successful.
As an improvement, I'm still working on prepared statements. However, so far all attempts ended in failure.