How to audit changes to Access Table Data? 3

[Valeriya]

I've never used a VBA in my Access parctise, so I would really appriciate, if someone would help me with a little advise (maybe it could be done with an append Query?):
I have two tables:

1st table- is dbo_Customer linked to my Acces Database and I don't know who exactly and when makes an updates to this table. I know it happens overnight...

2nd table- is0000 CONS ACCOUNT my own Access Table that partially gets the info from dbo_CustomerTable, and has different field's names for the same entity than in dbo_Customer. For example:
[dbo_Customer].[CustName]=[0000 CONS ACCOUNT].[AccountName]

[dbo_Customer].[REP_Name]=[0000 CONS ACCOUNT].[REP] and so on...

1. What kind of tool can I use to track all the changes happened overnight?

2. Where I Have to specify what fields should be matching?

3. Where and how should I request an update or to make those changes to my Access Table?

2. How can I produce any kind of output saying those particular changes were apllied (giving some kind of list of when and what exactly have been changed, like a macro that would automatically sends the e-mail to certain people, saying that Account Manager has been changed from ...to...).)?

Thank you in advance,
Valeriya

 

[obli]

I'm not shilling for this author but from what I've seen of his example here, I am impressed. Maybe it can show you how to do what you need done.

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AuditTrail.mdb

 

[Valeriya]

Thank you very much, obli!
I'm going try use it..

 

[rodrunner79]

Also, try this one

http://allenbrowne.com/AppAudit.html

 

[Valeriya]

Thanks a lot, roadrunner79!
This article talks exactly what I'm looking for!

 

[Valeriya]

Unfortunately, both articles are supporting the case when your interface limits users to manipulating data only through forms. My case is different all the updates to my linked tables are made by IT DBA and my role is to track only changes related to my department and update them to my Access Table?
Any other thoughts?

Thanks again!

 

[PHV]

As a starting point you may play with the SQL code generated by the unmatched query wizard.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Valeriya]

That is a great tool! I've never known about it exists!
However, you were right, now I have to figure out how to find what exactly were not matching. Because the unmatched query just showing every diffrent record... I hope it will be a good starting point...
I really appriciate Your help, PHV!
Valeriya

 

[psemianonymous]

When they have big systems that do this sort of 'table importing', they go ahead and call it a system interface. It's complicated and you shouldn't expect this to be miraculously easy.

You're going to have to be able to track changes like:
* new records
* edited records
* deleted records

Each of these three changes will require a separate query to figure out what's changed. Good luck. --Peter