audit history 1

[RandyMyers]

Hello All,

I use SQL databases and Access front ends mainly connecting through ODBC.

Is there an easy way of tracking (auditing) changes made in the system? What I want is basically User A changes table B on date, old value is, new value is.

I have a method that can do this using a function that is tied to the Before Update event on a form. It works fine for standard forms, but does not work for sub forms. I have figured a method of always opening a popup form to make a change in the displayed sub form. This works but I believe there must be an easier way.

Anybody ever do this and find a good way of writing changed out to a history table?

 

[RandyMyers]

Oh, on the main menu the subform's name is in fact frmCashJournalSub... I verified this as you stated...

 

[jsteph]

Randy,
It looks like you need to change the basAddHist function to accept a form object instead of a string.

What's happening is that you're passing the subform's name, then trying to access it as a form on it's own. It has to be accessed as:
1. A form variable that is set by passing the .Form property of the control of the parent.

2. A fully qualified parent.control.form path. Because if you pass the string then you'd need to know the parent's name *and* the subforms's .Name property, plus the fact that it's a subform.

So this would work (example 1):

Public Function basAddHist(Hist As String, [red]Frm As form[/red], MyKeyName As String, MyCtrl As Control)
<<dim's, etc removed >>                        
With tblHistTable
    .AddNew
    !MyKey = frm(MyKeyName)
<<  etc, etc>>

Or.. (example 2):

Public Function basAddHist(Hist As String, [red]Frm As String, Pform as string,[/red] MyKeyName As String, MyCtrl As Control)
<<dim's, etc removed >>                        
With tblHistTable
    .AddNew
    If pform <> "" then 
     [green]'then we know it's a parent/subform         
        'here the frm var. passed is the subform[/green]
        !MyKey = Forms(Pform).Controls(frm).Form(MyKeyName)
    Else 'just a single, parent form, so pform is blank
         [green]'here, frm is the actual form,no sub[/green]
        !mykey = forms(frm)(mykeyname)
    end if
<<  etc, etc>>

I prefer example 1, but I showed example 2 to to illustrate what was happening.
See if that works,
--Jim

 

[RandyMyers]

Thanks Jim,

I will definately look at that. BTW, I just finally figured out how to get the trigger to work and can audit updates and deletes directly on the table level... Here is the code for the trigger on the table...

CREATE trigger CashReceiptsAudit
on dbo.tblCashReceipts
for update, delete
as
if(select count(*) from inserted)>0
begin
if (select count(*) from deleted)>0
begin
insert tblCashAudit
(
Receipts,
Field,
dtChg,
OldVal,
NewVal,
UserName
)
select
i.Receipts,
'Transit',
getdate(),
d.Transit,
i.Transit,
suser_sname()
from
inserted i
inner join
deleted d
on
i.Receipts = d.Receipts
-- only if Transit changed
and i.Transit != d.Transit
end
begin
insert tblCashAudit
(
Receipts,
Field,
dtChg,
OldVal,
NewVal,
UserName
)
select
i.Receipts,
'Check Amount',
getdate(),
convert(varchar(255), d.CheckAmt),
convert(varchar(255), i.CheckAmt),
suser_sname()
from
inserted i
inner join
deleted d
on
i.Receipts = d.Receipts
-- only if CheckAmt changed
and i.CheckAmt != d.CheckAmt
end
begin
insert tblCashAudit
(
Receipts,
Field,
dtChg,
OldVal,
NewVal,
UserName
)
select
i.Receipts,
'Cash Amount',
getdate(),
convert(varchar(255), d.CashAmt),
convert(varchar(255), i.CashAmt),
suser_sname()
from
inserted i
inner join
deleted d
on
i.Receipts = d.Receipts
-- only if CashAmt changed
and i.CashAmt != d.CashAmt
end
begin
insert tblCashAudit
(
Receipts,
Field,
dtChg,
OldVal,
NewVal,
UserName
)
select
i.Receipts,
'Names',
getdate(),
d.Names,
i.Names,
suser_sname()
from
inserted i
inner join
deleted d
on
i.Receipts = d.Receipts
-- only if Names changed
and i.Names != d.Names
end
begin
insert tblCashAudit
(
Receipts,
Field,
dtChg,
OldVal,
NewVal,
UserName
)
select
i.Receipts,
'Description',
getdate(),
d.Description,
i.Description,
suser_sname()
from
inserted i
inner join
deleted d
on
i.Receipts = d.Receipts
-- only if Desription changed
and i.Description != d.Description
end
end
else
begin
insert tblCashAudit
(
Receipts,
Field,
dtChg,
UserName
)
select
Receipts,
'Record Deleted',
getdate(),
suser_sname()
from
deleted
end

 

[jsteph]

Randy,
You can use the syntax :
IF UPDATE(descritpion) Begin
...blah, blah
END

..to pick and choose which fields may have been updated.

On the face of it, you still want to keep triggers shorter than that and with fewer logic branches becase as I said, if anything goes wrong it's very difficult to tell, since somebody'll update a record and it may simply hang, say, due to a slight design change in the main table which now doesn't match the audit table, or vice versa, for instance.

But in general, the trigger is far mor reliable as far as catching any update--even if a user goes into the raw access/odbc-linked sqlserver table and directly edits it--the trigger will catch it.

One thing to consider--suser_sname() will return the logon to sql server, not the computer's windows login, unless you use windows nt trusted connections. So if you have all your odbc links set up as sa, for instance, you'll see all audits are by 'sa'. Not good. You can use the following:

declare @SQLusr varchar(50)
declare @Netuser varchar(50)
declare @mach varchar(50)
declare @app varchar(50)
declare @mac varchar(20)

SELECT  @mach =rtrim( hostname),@app = rtrim(program_name),@SQLusr = rtrim(loginame),@mac = net_address,@netuser = rtrim(nt_username) FROM master.dbo.sysprocesses WHERE spid = @@spid

This stores a lot of info in these variables, which you can put in your audit table. The net_addres (@mac) is the one I rely on the most, because it's the mac address of the actual computer. Also, when I make the odbc links (if I'm using odbc-linked tables, I manually put the dsn string in so the Application isn't the default "Microsof Access", which is generall all you'll see in the @app variable if you're using linked tables. I put the currentdb.name in the App section of the connect string so I can know which of my apps is doing what.
--Jim