Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1342
  • Last Modified:

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
0
pkimk
Asked:
pkimk
1 Solution
 
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
 
AzraSoundCommented:
You are putting the transaction codes in the right place, just need to apply whatever object it is youre using to it.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now