Begin Trans - Rollback - Commit Trans

Can someone please modify my coding, so that enable Begin Trans  - Rollback - Commit Trans.

=====================================================
Private Sub abAdd_Click()
On Error GoTo sysError
    Dim sCriteria As String
    Dim rst As ADODB.Recordset
   
    'Begin Trans here??
    SetupRes rst
    rst.Open ("SELECT * FROM addBook")
    rst.AddNew
        rst!abName = addressBook.abName.Text
        rst!abBirthday = addressBook.abBirthday.Text
        rst!abGender = addressBook.abGender.Text
        rst!abHomeAdd = Trim(addressBook.abHomeAdd.Text)
    rst.Update
    'Commit Trans here
    rst.Close    'close database
    Exit Sub
sysError:
    'Rollback here
    msgBox  Err.Number & " : " & Err.Description _
            & Chr(10) & Chr(10) & "Sub: abAdd_Click()"
    Unload Me
End Sub

Public Sub SetupRes(ByRef vRes As ADODB.Recordset)
    Set vRes = New ADODB.Recordset
    Set vRes.ActiveConnection = con
    vRes.CursorLocation = adUseClient
    vRes.LockType = adLockOptimistic
    vRes.CursorType = adOpenDynamic
    vRes.CursorLocation = adUseClient
End Sub
pkimkAsked:
Who is Participating?
 
sbmcCommented:
Hello pkimk

The Begintrans,CommitTrans and Rollback methods apply to the connection object. Code is as follows.

Hope it helps



Private Sub abAdd_Click()
On Error GoTo sysError
    Dim sCriteria As String
    Dim rst As ADODB.Recordset
     
    'Begin Trans here??
    SetupRes rst
    rst.Open ("SELECT * FROM addBook")
    con.BeginTrans
    rst.AddNew
        rst!abName = addressBook.abName.Text
        rst!abBirthday = addressBook.abBirthday.Text
        rst!abGender = addressBook.abGender.Text
        rst!abHomeAdd = Trim(addressBook.abHomeAdd.Text)
    rst.Update
    'Commit Trans here
    con.CommitTrans
    rst.Close    'close database
    Exit Sub
sysError:
    'Rollback here
    con.Rollback
    msgBox  Err.Number & " : " & Err.Description _
            & Chr(10) & Chr(10) & "Sub: abAdd_Click()"
    Unload Me
End Sub

Public Sub SetupRes(ByRef vRes As ADODB.Recordset)
    Set vRes = New ADODB.Recordset
    Set vRes.ActiveConnection = con
    vRes.CursorLocation = adUseClient
    vRes.LockType = adLockOptimistic
    vRes.CursorType = adOpenDynamic
    vRes.CursorLocation = adUseClient
End Sub
0
 
pinshahCommented:
Where are u getting the connection from? If Con is a Connection object and is a global variable then

Just do

Con.BeginTrans

Ur Recordset code


Con.CommitTrans


ErrorLabel:

cON.RollBackTrans
0
 
sbmcCommented:
Hello pkimk

The Begintrans,CommitTrans and Rollback methods apply to the connection object. Code is as follows.

Hope it helps



Private Sub abAdd_Click()
On Error GoTo sysError
    Dim sCriteria As String
    Dim rst As ADODB.Recordset
     
    'Begin Trans here??
    SetupRes rst
    rst.Open ("SELECT * FROM addBook")
    con.BeginTrans
    rst.AddNew
        rst!abName = addressBook.abName.Text
        rst!abBirthday = addressBook.abBirthday.Text
        rst!abGender = addressBook.abGender.Text
        rst!abHomeAdd = Trim(addressBook.abHomeAdd.Text)
    rst.Update
    'Commit Trans here
    con.CommitTrans
    rst.Close    'close database
    Exit Sub
sysError:
    'Rollback here
    con.Rollback
    msgBox  Err.Number & " : " & Err.Description _
            & Chr(10) & Chr(10) & "Sub: abAdd_Click()"
    Unload Me
End Sub

Public Sub SetupRes(ByRef vRes As ADODB.Recordset)
    Set vRes = New ADODB.Recordset
    Set vRes.ActiveConnection = con
    vRes.CursorLocation = adUseClient
    vRes.LockType = adLockOptimistic
    vRes.CursorType = adOpenDynamic
    vRes.CursorLocation = adUseClient
End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AzraSoundCommented:
You are putting the transaction codes in the right place, just need to apply whatever object it is youre using to it.
0
 
simonstreCommented:
u also could use the batch
(adlockbath) instead of adlockoptimistic.
Think it's faster, but i'm not sure.
0
 
pkimkAuthor Commented:
con define as ...
Public con As ADODB.Connection

Many Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.