Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2007
    Location
    College Park, Maryland
    Posts
    14

    Post Unanswered: Using RollbackTrans

    hi all,
    i have a large data transaction to saved in the database, how can i use RollbackTrans when i dont want to rollback all the commited data.. i just want to rollback the last or the last few transaction when error occurred.


    heres my few sample code:

    Code:
        bCommitted = False
        cnTracs.BeginTrans
        
        Call CopyMARCHLines
        Call CopyMARCHDetails
        Call CopyRegFees
        
        cnTracs.CommitTrans
        bCommitted = True
        
        MsgBox MESSAGE_01, vbInformation, Main_Menu_MDI.Caption
        Exit Sub
        
    Save_EH:
        If Not bCommitted Then
            cnTracs.RollbackTrans
        End If
        MsgBox "Save_EH: " & Err.Description, vbCritical, Main_Menu_MDI.Caption
        Err.Clear
    thanx! heaven
    Last edited by loquin; 03-29-08 at 03:12. Reason: changed font size & bold tags to [code] [/code] Try it. You'll like it.
    " a man that moves a mountain starts first with the little stones " - not a liar

    Janlie Macdovish
    Software Engineer
    College Park, MD

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Separate them into separate transactions?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Location
    College Park, Maryland
    Posts
    14
    what do u mean separate transaction?
    this transaction called Revision Year, like when the user click the start cmdButton all stored data will be revise... and im referring to more than 50thousand data.. and
    for instance, 70% already restored and disruption occurred(low-voltage situation). " RollbackTrans " will rollback everything and start again.

    pls be specified, or give some example.
    thanx
    " a man that moves a mountain starts first with the little stones " - not a liar

    Janlie Macdovish
    Software Engineer
    College Park, MD

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you post the code within the transactions please?
    If you are doing a bulk copy job/update, then it is a single transaction that cannot be split. However, if you are using a cursor/recordset approach (which will be slower!) you have the possibility of each insert, update, etc to be a single transaction
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Location
    College Park, Maryland
    Posts
    14
    heres my code:

    Code:
    Private Sub General_RY()
    Dim lCounter As Currency
    Dim lIndex As Long
    Dim sPIN As String
    Dim nSfx As Integer, nRY As Integer, iEffYear As Integer
    Dim dCreat_Dt As Date
    Dim bBegin_Trans As Boolean
    On Error GoTo Err_Handler
        cnTracs.BeginTrans
        bBegin_Trans = True
        lCounter = 100 / lAffect_TD
        prbRY.Value = 0
        lblPercent.Visible = True
        lblChanges.Visible = True
        Get_TDNO sTD_No
        For lIndex = 0 To lAffect_TD - 1
            If bCancel = True Then Exit Sub
            DoEvents
            TD_Information sTD_No(lIndex), sPIN, nSfx, dCreat_Dt, nRY, iEffYear  'fuction for updating
            DoEvents
            CreateRPUs sPIN, nSfx, dCreat_Dt, nRY   'fuction for updating
            DoEvents
            CreateTDs sTD_No(lIndex), sPIN, nSfx, iEffYear  'fuction for updating
            DoEvents
            Update_PreviousTD sTD_No(lIndex)            'fuction for updating 
            DoEvents
            prbRY.Value = IIf((prbRY.Value + lCounter) >= 100, 100, prbRY.Value + lCounter)
            lblPercent.Caption = "Complete " & Format(prbRY.Value, "%##0")
        Next
        cmdDone.Visible = False
        cmdClose.Caption = "&Close"
        lblChanges.Visible = False
        cnTracs.CommitTrans
        bBegin_Trans = False
        MsgBox "General Revision Year copy successfully.", vbInformation, Main_Menu_MDI.Caption
        Exit Sub
    Err_Handler:
        If bBegin_Trans Then
            cnTracs.RollbackTrans    'problem
            bBegin_Trans = False
        End If
        cmdDone.Visible = False
        cmdClose.Caption = "&Close"
        lblChanges.Visible = False
        lblPercent.Caption = ""
        lblPercent.Visible = False
        prbRY.Value = 0
        ErrMsg
    End Sub
    i'ved tried to use a temporary table for each affected table but its not working well and it was very slow. Can i have another option?

    Thank You!
    Last edited by loquin; 03-29-08 at 03:10. Reason: added [code] [/code] tags for clarity
    " a man that moves a mountain starts first with the little stones " - not a liar

    Janlie Macdovish
    Software Engineer
    College Park, MD

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    What kind of database are you using? some allow the use of savepoints within transactions
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Dec 2007
    Location
    College Park, Maryland
    Posts
    14
    im using SQL 2000.
    " a man that moves a mountain starts first with the little stones " - not a liar

    Janlie Macdovish
    Software Engineer
    College Park, MD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •