Solved

Begin Trans  - Rollback - Commit Trans

Posted on 2000-03-27
6
1,336 Views
Last Modified: 2012-06-27
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
Comment
Question by:pkimk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 

Expert Comment

by:pinshah
ID: 2663109
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
 
LVL 2

Expert Comment

by:sbmc
ID: 2663123
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
 
LVL 28

Expert Comment

by:AzraSound
ID: 2663134
You are putting the transaction codes in the right place, just need to apply whatever object it is youre using to it.
0
Industry Leaders: 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!

 
LVL 2

Accepted Solution

by:
sbmc earned 50 total points
ID: 2663174
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
 
LVL 1

Expert Comment

by:simonstre
ID: 2665059
u also could use the batch
(adlockbath) instead of adlockoptimistic.
Think it's faster, but i'm not sure.
0
 

Author Comment

by:pkimk
ID: 2667424
con define as ...
Public con As ADODB.Connection

Many Thanks.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question