tracking/approving data changes before they are written to live data

[nerdcore]

I have a client who has a database with an SQL 2000 backend and a Microsoft Access front end. It's huge (like 450 forms, 400 tables, 150 reports, etc.)

There is one form (it also has a subform) on which they would like to track any changes made, and NOT write these changes to the live data, but rather to keep them in a "holding" area where the supervisor could then pull them up (once every couple days) and check a box to approve the changes field by field, and then write the data to the database.

I have a couple ideas on how to do this and found a "poor man's" transaction log here:

http://www.tek-tips.com/faqs.cfm?spid=181&sfid=291&newpid=181

that seems to be what I want.

Before I start though I would like to get any input you guys may have as I haven't worked with an SQL backend before and maybe there is an easier way to do this. My current idea is to use the code in the link above and then have another form that lists the main record with a subform that shows the field by field changes with an "approved" checkbox. Then when they are done an update query would run to write the approved data to the database.

Thanks in advance for any help!

 

[MichaelRed]

The referenced faq only works with BOUND controls, so there may be some issues re the SQL Server tables. It should work with LINKED tables, but I believe this will create some bottlenecks.

A small change would need to be made in the calling as well, at least from the 'suggested' approach. The call sould need to be placed in the BEFORE UPDATE event, and the actual update would need to be canceled.

I think that in the process you are discussing, the overall schema will get much more complex than I would like to deal with. To get a reasonable display of the changes for the approval process, you will need to retrieve the original record, and the set of changes. Next, you will need to be able to relate the changes to the specific fields of the original and display the entire original with the changes juxtaposed to them.

A conseptually simpler approach would be to simply save the modified records to a temp table, or even to the same table with an additional field indicating the revision level. Comparision between the versions would be somewhat easier to retrieve and display.

From a broader perspective, the delay in review and approval seems to have other problems and issues to address. The largest is ye olde concurrency. How would you be dealing with multiple changes to the same record in a single interval between submission and approval? Would a second (or later) revision be to the currently approved version or to the pending changes? How wouold the disapproval of one affect a later revision?

For an app as large as you mention, maintenance of even the "FE" will be a major effort / issue. Introducing a delay in record approvals will complicate this even more.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places