BeginTrans、CommitTrans、RollbackTrans メソッドの例 (VB)

この例では、データベースの Titles テーブル内のすべての心理学書の書籍種別を変更します。BeginTrans メソッドで、Titles テーブルへのすべての変更を独立させるトランザクションを開始した後、CommitTrans メソッドで、変更を保存します。RollbackTrans メソッドを使うと、Update メソッドで保存した変更を変更前の状態に戻せます。

Public Sub BeginTransX()

    Dim cnn1 As ADODB.Connection
    Dim rstTitles As ADODB.Recordset
    Dim strCnn As String
    Dim strTitle As String
    Dim strMessage As String

    ' Open connection.
        strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
    Set cnn1 = New ADODB.Connection
    cnn1.Open strCnn

    ' Open Titles table.
    Set rstTitles = New ADODB.Recordset
    rstTitles.CursorType = adOpenDynamic
    rstTitles.LockType = adLockPessimistic
    rstTitles.Open "titles", cnn1, , , adCmdTable
    
    rstTitles.MoveFirst
    cnn1.BeginTrans

    ' Loop through recordset and ask user if she wants 
    ' to change the type for a specified title.
    Do Until rstTitles.EOF
        If Trim(rstTitles!Type) = "psychology" Then
            strTitle = rstTitles!Title
            strMessage = "Title: " & strTitle & vbCr & _
            "Change type to self help?"

            ' Change the title for the specified
            ' employee.
            If MsgBox(strMessage, vbYesNo) = vbYes Then
                rstTitles!Type = "self_help"
                rstTitles.Update
            End If
        End If

            rstTitles.MoveNext
    Loop

    ' Ask if the user wants to commit to all the 
    ' changes made above.
    If MsgBox("Save all changes?", vbYesNo) = vbYes Then
        cnn1.CommitTrans
    Else
        cnn1.RollbackTrans
    End If

    ' Print current data in recordset.
    rstTitles.Requery
    rstTitles.MoveFirst
    Do While Not rstTitles.EOF
        Debug.Print rstTitles!Title & " - " & rstTitles!Type
        rstTitles.MoveNext
    Loop

    ' Restore original data because this
    ' is a demonstration.
    rstTitles.MoveFirst
    Do Until rstTitles.EOF
        If Trim(rstTitles!Type) = "self_help" Then
            rstTitles!Type = "psychology"
            rstTitles.Update
        End If
        rstTitles.MoveNext
    Loop

    rstTitles.Close
    cnn1.Close

End Sub