Question about audit trail code

[bruboj]

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

[Allen Browne]

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

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

<br****@aol.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

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

[Allen Browne]

--
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.
<br****@aol.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

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

[DFS]

Allen Browne wrote:

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

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 MSysRelationships.

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
<br****@aol.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

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

[Allen Browne]

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" <no****@DFS.com> wrote in message
news:E4******************@fe03.lga...

Allen Browne wrote:

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

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 MSysRelationships.

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

[DFS]

Allen Browne wrote:

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.
I might. Also interesting and challenging would be constructing a
checkpoint and rollback system based on the audit trail.

"DFS" <no****@DFS.com> wrote in message
news:E4******************@fe03.lga...

Allen Browne wrote:

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

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
MSysRelationships.

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