Results 1 to 5 of 5

Thread: Access Track Changes to Data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Access Track Changes to Data

    Hi

    I'm designing a database which has an Excel front-end and uses Access for data store.

    Each save from Excel to Access is a normalised table showing data for: A (one) Client which has 0 or more Jobs and where each Job has 0 or more Transactions

    If I need to reload previous saved states for a given job or Client any tips/suggestions on how to structure the data?

    I'm assuming I'll need a time-stamp field (Excel or Access generated) when data is appended to a [Transactions?] table


    Also, if a transaction has value 10, saved with time stamp Today and tomorrow, value remains 10 but saved with tomorrow's time stamp, over time, this would create a large amount of data where only the time stamp field is changing.

    Is there a better solution to save relevant change and allow roll back to previous save points?

    TIA,
    Jack


  2. #2
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    216
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Track Changes to Data

    See my post in this thread:

    https://www.mrexcel.com/forum/micros...st-method.html

    I've not used this audit feature to roll back records, but so long as you have all the time-stamped changes, it should be pretty straight forward to run update queries that return each field to its previous value in reverse chronological order until the ID of the state you're looking to roll back to. (A bonus with this method is being able to roll back just individual fields.)

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Access Track Changes to Data

    Hey JonXL, thanks for sharing the link, it points to a $16 downloadable database which contains an example of a database that can roll-back, is this correct?
    Last edited by JackDanIce; Oct 21st, 2019 at 05:13 AM.


  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access Track Changes to Data

    That is a database with an example of an audit trail (tracking changes). See above the suggestion is that you can "rollback" changes by writing queries to work with the audit table to reverse changes but strictly speaking that means new changes to undo the old changes. This is not the same as true rollback which means not committing uncommitted changes.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Access Track Changes to Data

    Thanks xenou, apologies for mix up with terms roll-back and undoing changes.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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