Access Track Changes to Data

[JackDanIce]

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

 

[JonXL]

See my post in this thread:

https://www.mrexcel.com/forum/microsoft-access/1112353-advice-best-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.)

 

[JackDanIce]

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?

 

[xenou]

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.

 

[JackDanIce]

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