Update Access table with another linked dbo_Table 1

[Valeriya]

Hey, Guys!
Trying to figure out how to write (maybe an append query)which is going to update my Access_Table if Particular Fields from dbo_Table are not matching with my Access_Table.
Also I need to develop some kind of tool that would track these changes(maybe macro that would automatically send the e-mail to certain people, saying that Account Manager has been changed from ...to...).For example:

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

Do I really need to specify or to write IF Statement for each field that is not matching and how it's usually done?

Thank you in advance!

Valeriya

 

[assets]

Microsoft has information about audit tracking. I use it in a memo field that show wate the field was and who changes. You could play with this to email you.

the before update properties:Audit Trail()
and save this code to a module.
---------------------------------
Function AuditTrail()
On Error GoTo Err_Handler

Dim MyForm As Form, C As Control, xName As String
Set MyForm = Screen.ActiveForm

'Set date and current user if form has been updated.
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"Changes made on " & Date & " by " & fOSUserName() & ";"

'If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"New Record """
End If

'Check each data entry control for change and record
'old value of Control.
For Each C In MyForm.Controls

'Only check data entry type controls.
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
' Skip Updates field.
If C.NAME <> "Updates" And C.NAME <> "DepartmentID" And C.NAME <> "NextSchedMaint" And C.NAME <> "Total Maintenance" And C.NAME <> "DateAcquired" And C.NAME <> "total depreciation" And C.NAME <> "Make" And C.NAME <> "Text1063" And C.NAME <> "Text1062" And C.NAME <> "Text11424" And C.NAME <> "Text11437" Then

' If control was previously Null, record "previous
' value was blank."
If IsNull(C.OldValue) Or C.OldValue = "" Then
MyForm!Updates = MyForm!Updates & Chr(13) & _
Chr(10) & C.NAME & "--previous value was blank"

' If control had previous value, record previous value.
ElseIf C.Value <> C.OldValue Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
C.NAME & "==previous value was " & C.OldValue
End If
End If
End Select
Next C

TryNextC:
Exit Function

Err_Handler:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
End If
Resume TryNextC
End Function
-------------------------------------------

Hopes this helps


Never give up never give in.

There are no short cuts to anything worth doing

 

[Valeriya]

Thank you for your advice, but honestly I confused a little more. I've never used a VBA in my Access parctise, so I would really appriciate, if someone would expalin me:
Again 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- is 0000 CONS ACCOUNT my own Access Table that partially gets the info from dbo_CustomerTable, and has different field's names 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?
4. How can I produce any kind of output saying those particular changes were apllied (giving some kind of list of what exactly have been changed)?

Thank you in advance,
Valeriya