Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

commit/rollback facilities?

Discussion in 'Information Technology' started by hstijnen, Jul 28, 2006.

  1. hstijnen

    hstijnen
    Expand Collapse
    Guest

    Hi,
    Does MS-Access 2000 has any commit and especially Rollback facilities? E.g.
    I have a form, apply some updates, and then when closing the form, I want to
    commit or Undo/Rollback the changes. Is that possible?

    Thanks for help

    Henk
     
  2. Loading...

    Similar Threads Forum Date
    India Age-Limit for Senior Citizens Facilities Breaking News Nov 24, 2010
    Better Facilities at Gurdwaras for Sikh Pilgrims Assured (Islamabad) Sikh Sikhi Sikhism Mar 22, 2010
    Lack of honesty in improving Medical Facilities in Rural Areas. Hard Talk Feb 12, 2010
    Sikh News 'Punjab to improve healthcare facilities' (New Kerala) Breaking News Sep 5, 2008
    Pentagon Aims to Disperse Facilities (washingtonpost.com) Interfaith Dialogues May 20, 2005

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    When you are working with the data programmatically, you can commit or
    rollback an entire transaction. See help on BeginTrans.

    Bound forms use their own transactions (e.g. for rolling back a deletion),
    but these are not exposed.

    In Access 2000 and later, it is actually possible to open a transaction, and
    then open a recordset inside that transaction, and then assign it to the
    Recordset of the form so that the bound form's records are operating inside
    your transaction. Then you can commit or rollback before closing the form.

    In practice, however there are some problems and limitations with that
    approach. It becomes very clumsy in a multi-user environment when multiple
    users are holding transactions open for extended periods of time and can
    rollback over the top of each other. Additionally, it is not possible to do
    this with subforms that have a LinkMasterFields/LinkChildFields since Access
    reloads the subform whenever the main form changes record, and so the
    assignment of the recordset does not survive. On top of that, my experiments
    suggested that this was not as stable as Access normally is.

    So, in practice, my answer would be, No. You cannot do that short of copying
    the records into a temp table, editing them there, and then sorting out any
    multi-user conflicts yourself.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "hstijnen" <hstijnen@discussions.microsoft.com> wrote in message
    news:C8BF2268-F4D6-4FA6-B0C4-8080A1A3F06E@microsoft.com...
    > Hi,
    > Does MS-Access 2000 has any commit and especially Rollback facilities?
    > E.g.
    > I have a form, apply some updates, and then when closing the form, I want
    > to
    > commit or Undo/Rollback the changes. Is that possible?
    >
    > Thanks for help
    >
    > Henk
     
  4. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

  5. hstijnen

    hstijnen
    Expand Collapse
    Guest

    Thanks, Albert,

    Am I right that this implies that on the moment I leave a record in a form
    (by mouseclick or cursor), the changes in that record are committed? Is there
    a function to test for changes?

    "Albert D.Kallal" wrote:

    > You can undo changes to a record made in a form by simply going
    >
    > me.undo
    >
    > As mentioned, there is also the begin trans, and commit, but they do not
    > apply to forms, but only code that you write.
    >
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
    >
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Correct. The changes are committed as soon as you move to another record.
    They are also committed if you requery the form, change its RecordSource,
    apply filtering, change sorting, close the form, close Access, press
    Shift+Enter, choose Save Record on the Records menu, move into a different
    subform, and so on.

    Use the BeforeUpdate event of the form to perform any tests or validation
    needed before the record is saved.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "hstijnen" <hstijnen@discussions.microsoft.com> wrote in message
    news:A30817B5-76D3-44EC-B03D-297983B99309@microsoft.com...
    > Thanks, Albert,
    >
    > Am I right that this implies that on the moment I leave a record in a form
    > (by mouseclick or cursor), the changes in that record are committed? Is
    > there
    > a function to test for changes?
    >
    > "Albert D.Kallal" wrote:
    >
    >> You can undo changes to a record made in a form by simply going
    >>
    >> me.undo
    >>
    >> As mentioned, there is also the begin trans, and commit, but they do not
    >> apply to forms, but only code that you write.
    >>
    >>
    >> --
    >> Albert D. Kallal (Access MVP)
    >> Edmonton, Alberta Canada
    >> pleaseNOOSpamKallal@msn.com
    >> http://www.members.shaw.ca/AlbertKallal
     
  7. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    > Thanks, Albert,
    >
    > Am I right that this implies that on the moment I leave a record in a form
    > (by mouseclick or cursor), the changes in that record are committed? Is
    > there
    > a function to test for changes?


    Yes, you can check the Dirty property.

    if me.Dirty = True then
    ' changes have been made...
    else
    ' changes have not been made
    end if

    You can also use the dirty proeryt to force a disk write...

    if me.Dirty = True then
    me.Dirty = false ' force data to disk
    end if

    So, to prompt the user to write data to disk, you could put the following
    code in the before update event..


    If Me.Dirty = True Then

    If MsgBox("save data?", vbQuestion + vbYesNo, "Save") <> vbYes Then
    Me.Undo
    End If
    End If

    The above means if you navigate to another record, and don't change
    anything, then the user would not be prompted to save. If you edit
    something, and then try to move to anther record, the before update event
    fires. We simply execute a me.undo to discard the changes...

    Note that the before update event also has a cancel event, but that would
    also stop the record navigation from occurring, and keep the user on the
    current record if we set cancel = true.

    By the way, since the before update event DOES NOT fire unless the record is
    edited, then we actually don't need the dirty test...

    The following code would suffice....

    If MsgBox("save data?", vbQuestion + vbYesNo, "Save") <> vbYes Then
    Me.Undo
    End If

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     

Share This Page