By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Track changes

P: 25
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?
Aug 9 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
9 Replies


ariful alam
100+
P: 185
What changes about are you talking? Changes of Data (Insert/Edit/Delete) in tables or any other changes?
Aug 9 '12 #2

P: 25
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.
Aug 9 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,283
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.
Aug 9 '12 #4

P: 25
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.
Aug 9 '12 #5

twinnyfo
Expert Mod 2.5K+
P: 3,283
To my knowledge, Access does not keep track of anything like that inherently. You have to track it through code and store it somewhere.
Aug 9 '12 #6

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

Thanks again.
Aug 9 '12 #7

zmbd
Expert Mod 5K+
P: 5,397
Rupali,
Please do a search on this site... several threads cover this EXACT topic and offer some very good solutions.

-z
Aug 9 '12 #8

P: 25
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?
Aug 9 '12 #9

zmbd
Expert Mod 5K+
P: 5,397
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
Aug 9 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.