Backing Out Changes

[Guest]

I'm using ADO from XL2000 to work with an Access Database. I've been
requested to create a "back-out' procedure, and I want to make sure I'm
thinking along the right lines. Feel free to analyze and respond to the
following:

Create a universal "Back-Out" table to store the changed data. Ex: Changed
was EmpName from "Clay" to "Ralph". EmpNum (key) is 256. The Back-Out record
would look like this:

Back-Out.[TableName] = 'EmpData'
Back-Out.[KeyField] = 'EmpNum'
Back-Out.[Key] = '256'
Back-Out.[FieldName] = 'EmpName'
Back-Out.[OldData] = 'Clay'
Back-Out.[NewData] = 'Ralph'

And the VBA SQL string would look like this:

SQLString = "UPDATE " & tablename & " SET [" & fieldname & "] = '" & olddata
& "' WHERE [" & keyfield & "] = '" & key & "';"

Resulting in this:

SQLString = "UPDATE EmpData SET [EmpName] = 'Clay' WHERE [EmpNum] = '256';

 

[Guest]

One potential issue with a universal backout table is that data types might
not be compatible. The OldData and NewData fields would might end up holding
decimal, dates, numbers, etc. depending upon what you are changing. You'd
also need to think about what happens if you update multiple fields in the
same row in one SQL statement. And how would you do the update if you made
several changes to Last Name in a row, for instance, and wanted to choose
which saved record to pick.

 

[Jamie Collins]

I'm usingADOfrom XL2000 to work with an Access Database. I've been
requested to create a "back-out' procedure, and I want to make sure I'm
thinking along the right lines. Feel free to analyze and respond to the
following:

Create a universal "Back-Out" table to store the changed data. Ex: Changed
was EmpName from "Clay" to "Ralph". EmpNum (key) is 256. The Back-Out record
would look like this:

Back-Out.[TableName] = 'EmpData'
Back-Out.[KeyField] = 'EmpNum'
Back-Out.[Key] = '256'
Back-Out.[FieldName] = 'EmpName'
Back-Out.[OldData] = 'Clay'
Back-Out.[NewData] = 'Ralph'

And the VBA SQL string would look like this:

SQLString = "UPDATE " & tablename & " SET [" & fieldname & "] = '" & olddata
& "' WHERE [" & keyfield & "] = '" & key & "';"

Resulting in this:

SQLString = "UPDATE EmpData SET [EmpName] = 'Clay' WHERE [EmpNum] = '256';

What about INSERTs and DELETEs? (remember a logical update is a delete
and an insert.)

I'd suggest a 'tracking log' approach:

"A tracking log captures the sequence of modifications that have been
applied to a single table, the table being tracked. The tracking log
allows the monitored table to be reconstructed as of any time in the
past. This feature can be used to undo inadvertent modifications or to
restore the table to a previous, consistent state ... The schema of a
tracking log comprises the columns of the monitored table along with a
single timestamp column. Its key is simply the primary key of the
monitored table and the timestamp column ... A tracking log can also
contain auxiliary columns, such as Who_Changed"

See, Developing Time-Oriented Database Applications in SQL, Snodgrass,
R. T. (2000),
Chapter 8: Retaining a Tracking Log.

This classic book is available in pdf as free download:
http://www.cs.arizona.edu/people/rts/tdbbook.pdf

Note that because Access/Jet does not support triggers you are going
to have to figure out a way of ensuring the tracking log is kept in
sync with updates to the monitored table's data.

Jamie.

--

 

[Guest]

mray29:
Your comments are right on. That format of backout table won't work. Too
many variables. I thought about making another field for "Type" but figured
that would be even more difficult.

Jamie,
I've tried to sell them on SQL Server, but they're not biting. They don't
want to install anything on their servers.

I like the idea of a time-oriented database. I think that's what I'll do.
I'll start reading through that PDF - I need all the ehlp I can get.

A quick question - is it bad form (normalization?) to put a "current" flag
field in the tables? That way if someone makes a change, and it's wrong, we
can simply turn on the "Current" flag for another record.

Anyone else need a beer? (and it's only 10:40 am...)

 

[Guest]

I've tried to sell them on SQL Server, but they're not biting. They don't
want to install anything on their servers.

I didn't mention SQL Server (note that 'SQL' in the book title refers to the
language and not SQL Server; indeed, the book contains Access-specific
sections). If you mentioned SQL Server in relation to triggers then bad luck
your proposal didn't succeed.

I like the idea of a time-oriented database. I think that's what I'll do.
I'll start reading through that PDF - I need all the ehlp I can get.

A quick question - is it bad form (normalization?) to put a "current" flag
field in the tables? That way if someone makes a change, and it's wrong, we
can simply turn on the "Current" flag for another record.

You'd have to ensure only one row for each entity had the 'current = Y' flag
and if you can't/won't use a table level CHECK constraint then I think you
would need to denormalize to achieve this.

One thing that would bother me is that you would be changing the common
meaning of current because you could have rows with a timestamp more recent
than the one flagged as 'current' e.g.

{1, #2007-01-01 00:00:00#, 'N'}
{1, #2007-01-02 00:00:00#, 'Y'} <--- 'current' flag
{1, #2007-01-03 00:00:00#, 'N'}
{1, #2007-01-04 00:00:00#, 'N'} <--- most recent

Perhaps a better approach would be to have a separate table to mode the
considered 'current' row e.g. entity key plus the timestamp (FOREIGN KEY) and
PK'ed on just the entity key (a variation on the bi-temporal model).

<log table>
{1, #2007-01-01 00:00:00#}
{1, #2007-01-02 00:00:00#}
{1, #2007-01-03 00:00:00#}
{1, #2007-01-04 00:00:00#}

<'consider current' table>
{1, #2007-01-02 00:00:00#}


I think the better way to perform an 'undo' would be to *delete* the rows
with a more recent timestamp:

<log table before undo>
{1, #2007-01-01 00:00:00#}
{1, #2007-01-02 00:00:00#}
{1, #2007-01-03 00:00:00#}
{1, #2007-01-04 00:00:00#}

<log table after undo>
{1, #2007-01-01 00:00:00#}
{1, #2007-01-02 00:00:00#}

thus retaining the common meaning of current = most recent.

If you needed 'redo' functionality then deleting is out of the question and
you are back to the separate table (or flag) again, but note than you'd need
to delete the rows when the redo history is no longer required e.g. as
happens in Word when you redo the last actions then type something and those
three undone actions are then (and only then) lost.

Anyone else need a beer? (and it's only 10:40 am...)

Make mine a Duvel Seriously, if it's 'just to steady the nerves' then
consult a doctor.

Jamie.

--

 

[Guest]

heh heh - the beer was a joke. I actually gave up booze several years ago.
Thanks for the concern, though.

I have found myself under the gun to get this done quickly. I have a hard
deadline of 2:00 today. I've read through some of that PDF and I like what I
see. Problem is, I don't have time to study it properly and implement it in
this situation.

I like your ideas, and I will likely implement them here. Thank you for all
your input.

 

[Guest]

heh heh - the beer was a joke. I actually gave up booze several years ago.
Thanks for the concern, though.

And I was deadly serious about the Duvel <g> but only the one and after the
kids have gone to bed ([sings] Life in the fast lane...)

Jamie.

--