Question about audit trail code

**Allen Browne** · Nov 13 '05, 08:37 AM

Re: Question about audit trail code

The routines generate a record based on the primary key value. The primary
key must be a Number type field - not a Text field, because of the way the
variables are passed to the procedures and the way the SQL strings are
built. Although AutoNumber is probably the most common kind, an Integer or
Long Integer primary key would be okay.

BTW, the site will be moving to a different server soon, so the best address
to find it will be:

Allen Browne - Creating an Audit Log

http://allenbrowne.com/AppAudit.html

How to create an audit log to record the history of changes to records in a Microsoft Access database - edits, inserts, and deletions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<bruboj@aol.com > wrote in message
news:1109109990 .473451.253620@ o13g2000cwo.goo glegroups.com.. .[color=blue]
>I created an audit trail for my access 97 database using code I found
> at: http://members.iinet.net.au/~allenbrowne/AppAudit.html
> One of the limitations stated for the code is "each table to be audited
> must have an AutoNumber primary key". Can someone explain why the
> automunber field has to be the primary key? It seems that the
> autonumber field would be unique by default. I have another field that
> needs to be unique but there is nothing to prevent the user from
> entering duplicate values without making it the primary key or by
> adding code. If I understood this limitation further, I may be able to
> come up with a solution.
>
> Thanks,
>
> Michael[/color]

**Allen Browne** · Nov 13 '05, 08:37 AM

Re: Question about audit trail code

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<bruboj@aol.com > wrote in message
news:1109109990 .473451.253620@ o13g2000cwo.goo glegroups.com.. .[color=blue]
>I created an audit trail for my access 97 database using code I found
> at: http://members.iinet.net.au/~allenbrowne/AppAudit.html
> One of the limitations stated for the code is "each table to be audited
> must have an AutoNumber primary key". Can someone explain why the
> automunber field has to be the primary key? It seems that the
> autonumber field would be unique by default. I have another field that
> needs to be unique but there is nothing to prevent the user from
> entering duplicate values without making it the primary key or by
> adding code. If I understood this limitation further, I may be able to
> come up with a solution.
>
> Thanks,
>
> Michael
>[/color]

**DFS** · Nov 13 '05, 08:37 AM

Re: Question about audit trail code

Allen Browne wrote:[color=blue]
> The routines generate a record based on the primary key value. The
> primary key must be a Number type field - not a Text field, because
> of the way the variables are passed to the procedures and the way the
> SQL strings are built. Although AutoNumber is probably the most
> common kind, an Integer or Long Integer primary key would be okay.
>
> BTW, the site will be moving to a different server soon, so the best
> address to find it will be:
> http://allenbrowne.com/AppAudit.html[/color]

Allen,

Looks like a handy program.

I notice you don't log changes made for relationships containing cascaded
updates or deletes. You probably already know this, but if you wanted to
track those changes, it can be accomplished by polling MSysRelationshi ps.

Column grbit will contain:
0 if neither cascade updates or deletes is set
256 if cascade updates only is set
4096 if cascade deletes only is set
4352 if both cascade updates and deletes is set.

If I contribute the code, will you look at it, or include it in your system?

Thanks

[color=blue]
> <bruboj@aol.com > wrote in message
> news:1109109990 .473451.253620@ o13g2000cwo.goo glegroups.com.. .[color=green]
>> I created an audit trail for my access 97 database using code I found
>> at: http://members.iinet.net.au/~allenbrowne/AppAudit.html
>> One of the limitations stated for the code is "each table to be
>> audited must have an AutoNumber primary key". Can someone explain
>> why the automunber field has to be the primary key? It seems that
>> the autonumber field would be unique by default. I have another
>> field that needs to be unique but there is nothing to prevent the
>> user from entering duplicate values without making it the primary
>> key or by adding code. If I understood this limitation further, I
>> may be able to come up with a solution.
>>
>> Thanks,
>>
>> Michael[/color][/color]

**Allen Browne** · Nov 13 '05, 08:38 AM

Re: Question about audit trail code

That would need a fairly serious rework of the concept.

As currently set up, each table being edited has to be duplicated with
changed indexes and rules and additional fields to record the date/time and
user who made the change. So any tables involved in cascades would need
their matching audit table created as well, to write the audit to.
Alternatively, the audit structure could be handled relationally where we
log the table name, field name, old value, new value, date/time, and user,
on a field-by-field basis instead of a table-by-table basis.

Once the structure is in place, it would be possible to read the attributes
of the relation as you suggest, and identify the kind of change that will
occur (cascade-update, cascade-delete, cascade-to-null, or a combination),
and log the change.

Another aspect is that a cascade to a related table can also have further
cascades to other tables, so you would need to write the code recursively to
handle those as well. From memory, JET does not block circular cascades, so
you would need to test each branch of the cascade to see if that occurs
anywhere. Sounds like quite an interesting challenge if you feel like
persuing it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DFS" <nospam@DFS.com > wrote in message
news:E4USd.1393 7$eS5.8669@fe03 .lga...[color=blue]
> Allen Browne wrote:[color=green]
>> The routines generate a record based on the primary key value. The
>> primary key must be a Number type field - not a Text field, because
>> of the way the variables are passed to the procedures and the way the
>> SQL strings are built. Although AutoNumber is probably the most
>> common kind, an Integer or Long Integer primary key would be okay.
>>
>> BTW, the site will be moving to a different server soon, so the best
>> address to find it will be:
>> http://allenbrowne.com/AppAudit.html[/color]
>
>
> Allen,
>
> Looks like a handy program.
>
> I notice you don't log changes made for relationships containing cascaded
> updates or deletes. You probably already know this, but if you wanted to
> track those changes, it can be accomplished by polling MSysRelationshi ps.
>
> Column grbit will contain:
> 0 if neither cascade updates or deletes is set
> 256 if cascade updates only is set
> 4096 if cascade deletes only is set
> 4352 if both cascade updates and deletes is set.
>
> If I contribute the code, will you look at it, or include it in your
> system?
>
> Thanks[/color]

**DFS** · Nov 13 '05, 08:38 AM

Re: Question about audit trail code

Allen Browne wrote:[color=blue]
> That would need a fairly serious rework of the concept.
>
> As currently set up, each table being edited has to be duplicated with
> changed indexes and rules and additional fields to record the
> date/time and user who made the change. So any tables involved in
> cascades would need their matching audit table created as well, to
> write the audit to. Alternatively, the audit structure could be
> handled relationally where we log the table name, field name, old
> value, new value, date/time, and user, on a field-by-field basis
> instead of a table-by-table basis.
>
> Once the structure is in place, it would be possible to read the
> attributes of the relation as you suggest, and identify the kind of
> change that will occur (cascade-update, cascade-delete,
> cascade-to-null, or a combination), and log the change.
>
> Another aspect is that a cascade to a related table can also have
> further cascades to other tables, so you would need to write the code
> recursively to handle those as well. From memory, JET does not block
> circular cascades, so you would need to test each branch of the
> cascade to see if that occurs anywhere. Sounds like quite an
> interesting challenge if you feel like persuing it.[/color]

I might. Also interesting and challenging would be constructing a
checkpoint and rollback system based on the audit trail.

[color=blue]
> "DFS" <nospam@DFS.com > wrote in message
> news:E4USd.1393 7$eS5.8669@fe03 .lga...[color=green]
>> Allen Browne wrote:[color=darkred]
>>> The routines generate a record based on the primary key value. The
>>> primary key must be a Number type field - not a Text field, because
>>> of the way the variables are passed to the procedures and the way
>>> the SQL strings are built. Although AutoNumber is probably the most
>>> common kind, an Integer or Long Integer primary key would be okay.
>>>
>>> BTW, the site will be moving to a different server soon, so the best
>>> address to find it will be:
>>> http://allenbrowne.com/AppAudit.html[/color]
>>
>>
>> Allen,
>>
>> Looks like a handy program.
>>
>> I notice you don't log changes made for relationships containing
>> cascaded updates or deletes. You probably already know this, but if
>> you wanted to track those changes, it can be accomplished by polling
>> MSysRelationshi ps.
>>
>> Column grbit will contain:
>> 0 if neither cascade updates or deletes is set
>> 256 if cascade updates only is set
>> 4096 if cascade deletes only is set
>> 4352 if both cascade updates and deletes is set.
>>
>> If I contribute the code, will you look at it, or include it in your
>> system?
>>
>> Thanks[/color][/color]

Question about audit trail code

Question about audit trail code

Comment

Comment

Comment

Comment

Comment