Help with transactions using Begin Trans, Edit, and CommTrans 
Author Message
 Help with transactions using Begin Trans, Edit, and CommTrans

I have put together the following code.  The General Declarations
declare the TransferRecords, and ModifyRecords Variable.  The first
Transaction Do Loop works.  It successfully transfers the records and
adds them to the ManifestDn table (or dynaset).  Where my problem is is
on the last Do Loop.  I am trying to make changes to the listed fields
and for some reason the changes never take effect.  Where I have null I
just want to erase that particular field in the record set.  In other
words the next comments are erased after the record is processed.

I have checked both the database and the dynasets and they both have
enabled the Update Property and Transaction Property.  I have also
tested the Variables using debug and they are displaying the proper
answer.

Any words of wisdom would be greatly appreciated.

Sub mnumProcess_Click ()

    'Obtain Date

    Dim mandate As Variant
    Dim DefVal As String
    Dim Msg As String
    Dim Title As String

    Msg = "Enter Manifest Date"
    Title = "Manifest Date"
    DefVal = Date + 1

    mandate = InputBox(Msg, Title, DefVal)  ' Get user input.

    'Close Open Forms

    Dim DateMsg
    Dim DateTitle
    Dim DgDef
    Dim ProcessAns

    Const MB_OK = 0, MB_OKCANCEL = 1    ' Define buttons.
    Const MB_YESNOCANCEL = 3, MB_YESNO = 4
    Const MB_ICONSTOP = 16, MB_ICONQUESTION = 32    ' Define Icons.
    Const MB_ICONEXCLAMATION = 48, MB_ICONINFORMATION = 64
    Const IDOK = 1, IDCancel = 2  ' Define other.

    DateMsg = "Priscilla must now close all forms using the customer and
manifest databases!  Is this OK?"""
    DgDef = MB_OKCANCEL + MB_ICONSTOP + MB_DEFBUTTON1
    DateTitle = "Entering Processing Mode!"

    ProcessAns = MsgBox(DateMsg, DgDef, DateTitle)

    If Not ProcessAns = IDCancel Then
    End If

    Unload frmCustomer
    Unload frmManifest
    Unload Customer_Table

    FrmWait.Show

    'Open DataBases and Tables

    Dim ManiDB As Database
    Dim CustDn As Dynaset
    Dim ManifestDn As Dynaset

    Set ManiDB = OpenDatabase("C:\manifest\manifest.mdb", False, False)

    'Search for Matching Files

    Set CustDn = ManiDB.CreateDynaset("SELECT Customer.* FROM Customer
WHERE [nxtdt] =#" & mandate & "#")

    'Copy to Manifest

    Set ManifestDn = ManiDB.CreateDynaset("SELECT Manifest.* FROM
Manifest")

    If Err Then
        TransferRecords = Err
        Unload FrmWait
        MsgBox "Priscilla is having problems processing your request."
        ManifestDn.Close
        CustDn.Close
        ManiDB.Close
        Exit Sub
    End If

    Do

        BeginTrans
        If CustDn.EOF Then Exit Do
        ManifestDn.AddNew

        ManifestDn("CustID") = CustDn("CustID")
        ManifestDn("Account") = CustDn("Account")
        ManifestDn("On Call") = CustDn("On Call")
        ManifestDn("Sharps") = CustDn("Sharps")
        ManifestDn("Rep") = CustDn("Rep")
        ManifestDn("Division") = CustDn("Division")
        ManifestDn("pudat") = CustDn("Nxtdt")
        ManifestDn("Hours") = CustDn("Hours")
        ManifestDn("Name") = CustDn("Name")
        ManifestDn("Address") = CustDn("Address")
        ManifestDn("City") = CustDn("City")
        ManifestDn("State") = CustDn("State")
        ManifestDn("Zip") = CustDn("Zip")
        ManifestDn("Phone") = CustDn("Phone")
        ManifestDn("Ext") = CustDn("Ext")
        ManifestDn("Contact") = CustDn("Contact")
        ManifestDn("Start") = CustDn("Start")
        ManifestDn("Frequency") = CustDn("Frequency")
        ManifestDn("Volume") = CustDn("Volume")
        ManifestDn("Supplies") = CustDn("Supplies")
        ManifestDn("Route") = CustDn("Route")
        ManifestDn("Location") = CustDn("Location")
        ManifestDn("Directions") = CustDn("Directions")
        ManifestDn("Next Notes") = CustDn("Next Notes")
        ManifestDn("Notes") = CustDn("Notes")

        ManifestDn.Update

    If Err Then
        TransferRecords = -1
        Rollback
    Else
        TransferRecords = 0
        CommitTrans
    End If

    If CustDn.EOF Then Exit Do

        CustDn.MoveNext

    Loop

    'Change Temporary Settings and Change Dates
    If Not CustDn.BOF Then CustDn.MovePrevious
    If Not CustDn.EOF Then CustDn.MoveFirst
    If CustDn.BOF Then
        MsgBox "No Manifests For Pricsilla to Process!"
        Unload FrmWait
        ManifestDn.Close
        CustDn.Close
        ManiDB.Close
        Exit Sub
    End If

    If Err Then
        ModifyRecords = Err
        Unload FrmWait
        MsgBox "Priscilla is having problems processing your request."
        ManifestDn.Close
        CustDn.Close
        ManiDB.Close
        Exit Sub
    End If

    Do

        BeginTrans
        If CustDn.EOF Then Exit Do

        CustDn.Edit

        Dim OldDate As Variant
        Dim NewDate As Variant
        Dim Frequency As Variant
        Dim OnCall As Variant
        Dim NextNotes As Variant

        OldDate = CustDn("Nxtdt")
        Frequency = CustDn("Frequency")
        NewDate = OldDate + (Frequency * 7)
        OnCall = CustDn("On Call")
        NextNotes = Null

        If OnCall = -1 Then NewDate = Null
        If Frequency = .07 Then NewDate = Null
        If Frequency = .05 Then NewDate = Null
        If Frequency = .03 Then NewDate = Null
        If Frequency = .02 Then NewDate = Null

        CustDn("Last") = OldDate
        CustDn("Nxtdt") = NewDate
        CustDn("Next Notes") = NextNotes

        CustDn.Update

        If Err Then
            ModifyRecords = -1
            Rollback
        Else
            ModifyRecords = 0
            CommitTrans
        End If

        If CustDn.EOF Then Exit Do
        CustDn.MoveNext

    Loop

    'Search for Manifests matching the required date
    'and Print them

    'Close Down Subroutine

    Unload FrmWait
    MsgBox "Done"

End Sub



Fri, 14 Aug 1998 03:00:00 GMT  
 Help with transactions using Begin Trans, Edit, and CommTrans

Quote:

> I have put together the following code.  The General Declarations
> declare the TransferRecords, and ModifyRecords Variable.  The first
> Transaction Do Loop works.  It successfully transfers the records and
> adds them to the ManifestDn table (or dynaset).  Where my problem is is
> on the last Do Loop.  I am trying to make changes to the listed fields
> and for some reason the changes never take effect.  Where I have null I
> just want to erase that particular field in the record set.  In other
> words the next comments are erased after the record is processed.

<stuff deleted>

Hi Chip,

Two suggestions:

1 - Why do you use a transaction to add just one record? Transactions
    are only useful when you make several updates which _logically_
    belong together. This way you start from one consistent state and
    end in another consistent state. If an error occurs in between, you
    can 'roll back' to the initial state, ie cancel all updates so far.

2 - Ok, whatever your reason may be, you're using a transaction. I think
    that there is a problem in the first loop:

Quote:
>     Do
>         BeginTrans
>         If CustDn.EOF Then Exit Do

    There is a transaction _left open_  when you exit the loop. This
    will affect the behaviour of the next transaction, which will be
    opened in the next loop. Since transactions can be nested, both
    will act together. I'd move the "BeginTrans" statement after the
    "If" statement to avoid this effect.

Quote:
>         ManifestDn.AddNew
>         ManifestDn("CustID") = CustDn("CustID")

<some code deleted>

Quote:
>         ManifestDn("Notes") = CustDn("Notes")
>         ManifestDn.Update
>     If Err Then
>         TransferRecords = -1
>         Rollback
>     Else
>         TransferRecords = 0
>         CommitTrans
>     End If
>     If CustDn.EOF Then Exit Do
>         CustDn.MoveNext
>     Loop

Hope this helps.
----------
Haluk Okur / SIMKO A.S.       (Siemens in Turkiye)

Tel   : +90 (216) 389-5940, ext 4563                             -\<,
Fax   : +90 (216) 306-2548                                  ___(*)/(*)___


Sat, 15 Aug 1998 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Help with transactions using Begin Trans, Edit, and CommTrans

2. begin trans - commit trans

3. BEGIN,COMMIT Trans

4. Use of Begin/End trans scope

5. Begin Trans..Commit with Client Access ?

6. ADO err with trans begin/commit ?

7. Begin trans / open a record set on a stored proc on sql server

8. Begin and commit trans

9. Stored Procedure and ADO.net Begin Transaction Code

10. Begin-Rollback-Commit Transaction

11. Workspace Transaction Begin/Commit/Rollback bug?

12. Limits on Transactions between Begin & Commit

 

 
Powered by phpBB® Forum Software