We build applications with Access front ends and SQL 2000 backends. One of the requests we get frequently is to track when data is changed and who changed it. Currently to do that we put code behind EVERY field to get the old value compare it to the new value and then if it is different put the information in a record log table. We then create a form so Management can search and view the changes. I am sure there has to be a better and easier way!
I know SQL has a transaction log but is that something I can link into an application to display changes in a particular DB? Does anyone have any ideas? it will take me about a week to do it the way we have been so far. . Here is what our log end up looking like now
Log_Seq LogID LogDate FieldName Was Is SubSeq MainSeq FormName
48394 Amelia Power 05/16/2007 Received 0 -1 10371 6302 BackGround Check Information