Track changes

[rupali]

Hello,

I want to know if we can keep track of the changes done in the database.
e.g: I have a database called db1.mdb
This gets updated from an application say app1.
Now I want to keep track of the changes done in the db1.mdb without making any changes to app1.

Is this possible? and if yes then how?

[ariful alam]

What changes about are you talking? Changes of Data (Insert/Edit/Delete) in tables or any other changes?

[rupali]

yes I want to track changes in data, if new data is inserted, or existing data updated or any deletions done.

Also the database I want to keep track changes is in MS Access.

[twinnyfo]

rupali,

You would have to track every edit done by the user, and store the table, field and change in a separate table, along with who changed it and when they changed it. What you are talking about is monitoring everything that goes on in your database.

Yes, it is possible to do this, however the effort required may not be worth the benefit.

The simplest way is to build a public function that writes to a separate table that only tracks changes. Every time a data field is updated, that function would fire, saving the table name, field name, data before, data after, who updated it and when.

There would also be ways of doing this looking at the dirty state of a form and cycling through all the controls to see if they have been updated.

Either way, it will require modifying all your forms and input interfaces so that everything trips this update tracking function.

Hope this gets you down the right track.

[rupali]

Hello twinnyfo,

It means I will have to modify the entire application by inserting a record in new table with all the details you mentioned above.

But I want to ask is there any other solution using which I wont have to modify the current application.
I mean anything in MS Access which must be logging some info of the changes done.

To tell in more detail:
I want to synchronize the MS Access database and PostgreSQL database.
So I was searching if we can track changes in data in MS Access.

[twinnyfo]

To my knowledge, Access does not keep track of anything like that inherently. You have to track it through code and store it somewhere.

[rupali]

Ok Thanks twinnyfo, for all your quick responses.
May be I will have to go by the solution you provided.

Thanks again.

[zmbd]

Rupali,
Please do a search on this site... several threads cover this EXACT topic and offer some very good solutions.

-z

[rupali]

ariful,

You can answer me if you have any solution to my question.

Without making any change to current application,
can I get the changes done in the database in MS Access.
like SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. This is inbuilt feature of sql server.
similarly do we have anything in MS Access?

[zmbd]

Rupali.

To answer your last question:
- no, you will have to make a change.
- there is no built in audit trail within access.
you must code it.

In addition to searching this website for various solutions to the audit trail question
Here's an example:

http://support.microsoft.com/kb/197592

-z