How do I check for changes in a database on insert / update?

pts.
Tags:
Microsoft Access
SQL Server
VB.NET
Visual Basic
Hi there, I have an interesting problem. I need to write a program that checks to see if data has been inserted or updated into a Microsoft access database or sql server database. The program needs to be compatible with both types of database, so using triggers is not possible as it wont work in access etc Anyone out there have any idea as to how to do this check? I will be using visual basic for the development, as I dont have much experience in vb.net, but Im willing to give .net a try. Any help would be greatly appreciated! Thanks Scoob

Answer Wiki

Thanks. We'll let you know when a new response is added.

Please provide more detail about how updates will be handled to the database so I can give you a better answer. For example, what is the threshold for accuracy? If you want 100% accuracy you must check each field in each row 1-by-1.

If there is a “low” risk that other applications would modify the data. You can start with a simply boolean flag (IsChanged = ?) to sync 2 databases. But if you are going to sync multiple databases to a centeralized master copy then you need something more elaborate.

Tell me more about the problem…

Good luck

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Mattaux
    I would suggest storing the changes you make to the database in the database itself that way you have an historical record and an audit trail. One way to do this is to create shadow/mirror tables of all the core tables in your database. These shadows would contain all the fields in the core table along with a seed, a record type, and some kind of work flow ID. Then, on INSERT, UPDATE OR DELETE commands, the application would write the record to the shadow before any amendments. In this scenario you would also have a control type table which would record the user, start and stop times and the work flow id etc. The application would have to populate this at the same time. Hope this helps
    0 pointsBadges:
    report
  • Stanton
    I design all my tables with an Updated by field and an Updated date field. This way I can tell when the last time a record in any table was updated. This also helps with debugging problems. Stanton
    0 pointsBadges:
    report
  • Aussiemate
    Most definitely, the field option, addition of fields; 'lastUpdated' and 'updatedBy'; should be made a part of every p/t or full timer programmers approach. Having this structure in place allows laymen to query database tables, giving a much sought, management the ability to gain their own stats, as well as the usual monthly reporting. remember KISS!
    0 pointsBadges:
    report
  • Aussiemate
    Most definitely, the field option, addition of fields; 'lastUpdated' and 'updatedBy'; should be made a part of every p/t or full timer programmers approach. Having this structure in place allows laymen to query database tables, giving a much sought, management the ability to gain their own stats, as well as the usual monthly reporting. remember KISS! Mike
    0 pointsBadges:
    report
  • Howard2nd
    In 'Access' you have to do your own "transaction" tracking. SQL Server can do tracking for you. In either case the "Update" fields are best practice for table design.
    30 pointsBadges:
    report
  • Onilke
    It depends on to what use you will put the fact of "changed", and if any or specific change is interesting. Each of the databases would need a way to make the change visible. The before mentioned update fields are only interesting if your program checking for change is able to determinate that this is actually a new date or username. These fields never tell their former values, unless you keep an additional table of every transaction (you might need huge space for this!). The simplest way I can think of, is to supply every interesting table with the boolean field "Changed", being set to true with each insert or update AND reset to false by the program checking for change, after the fact of change is reported.
    95 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following