How to create an audit trail of record changes in table in Access200

[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 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

 

[lynchg]

Did you get an answer to this in the Access queries forum?

 

[Valeriya]

Not really I was advised by PHV to use the SQL code generated by the unmatched query wizard As a starting point .
That was a great point, however I'm stacked again:
Now I have to figure out how to find what exactly were not matching. Because the unmatched query just showing every diffrent record...
Wrote another If statement, but it still doesnt make any differnce to what I'm actually trying to achive:

AccountName: IIf([dbo_Customer].[CustName]<>[0000 CONS ACCOUNT].[AccountName],"Update",[dbo_Customer].[CustName])


Thanks,
Valeriya

 

[Zion7]

Maybe a function, in the BeforeUpdate event of each, relevant field.(you implied, only some fields need tracking ".2").
Using the OldValue & Value properties.

Once you have those 2 values, append to a "Changes" table.

Function Edit(ctl As Control)
Dim varRecord As Variant, SQL As String

varRecord = DLookUp("fkCustomerID","tblEdit")

If IsNull(varRecord) Then
SQL = "INSERT INTO tblEdit(fkCustomerID,txtField,txtOldValue,txtNewValue,txtDate,txtUser)" & _
"VALUES(" & Me.pkCustomerID & ",'" & ctl.Name & "','" & ctl.OldValue & _
"','" & ctl.Value & "',#" & Date & "#,'" & Application.CurrentUser & "')"

Else
SQL = "UPDATE tblEdit.....
End If

CurrentProject.Connection.Execute SQL

End Function

 

[Valeriya]

Thanks a lot! I would definately try your method Zoion7!

 

[NvrSaDi]

Several methodologies I use for comparing old and new tables... This process can probably be shortened, but works.

I archive a copy of the table the night before.
Archived and new tables must have unique identifiers.
I use sql statements in VB procedure to cycle thru tables and paste single matching records to 2 different tables and then copy those single records to 2 arrays. I then cycle thru the arrays and compare values.
If any 2 values are inconsistent, the code then copies those 2 records to a separate table, with comments. If one of the tables has no data as a result of a deleted record, I add a comment to identify missing records.

If there are a large number of fields, I limited the data copied to the separate table to just those with changes, otherwise, I copy the entire record.

When the dust settles, I have a table with matching records that identify the changes.