ADO Transaction

A Transaction is a series of SQL statements placed one below another. To maintain the integrity of transaction all the SQL statements must succeed together or none of the SQL statements must succeed. If the transaction fails all the changes made on the tables are fail.

<%

Dim myConnection As ADODB.Connection

Dim myRecordset As ADODB.Recordset

Set myConnection = New ADODB.Connection

Set myRecordset = New ADODB.Recordset

myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\BegDB\Biblio.mdb"

'—Open the connection --

myConnection.Open

'Determine if we conected.

If myConnection.State = adStateOpen Then

myRecordset.Open "SELECT * FROM TITLES", myConnection, _

adOpenDynamic, adLockOptimistic, adCmdTable

Else

MsgBox "The connection could not be made."

myConnection.Close

Exit Sub

End If

'—just to be sure --

myRecordset.MoveFirst

On Error GoTo transError

'—here is the top of the transaction sandwich --

myConnection.BeginTrans

While Not myRecordset.EOF

mcounter = mcounter + 1

myRecordset!Title = myRecordset!Title & "" 'so we don't really change it

myRecordset.Update

myRecordset.MoveNext

Wend

'—if we got here ok, then everything is written at once

myConnection.CommitTrans

myRecordset.Close

myConnection.Close

Exit Sub

transError:

myConnection.RollBack

myRecordset.Close

myConnection.Close

MsgBox Err.Description

%>