Access vs. SQL for audit trail of record changes?

[Jim M]

I've been playing with Allen Browne's audit code and found it very
useful. I need to track record insertions, deletions, and edits for
several tables. I am planning to replace Access with Microsoft SQL
server for my back end, but continue to use Access for the front end.

I understand I can create an audit trail of record changes in SQL at
the table level, instead of at the form level in Access.
I have been playing with Access since the days of 1.0, but an a newbie
with SQL. My application backend was fairly easy to move to SQL -
except for renaming a lot of tables.

My Questions:

1) How difficult is it to implement a record level audit capability in
SQL backend vs. Access.mdb front end? Since I have many forms that
interact with each of my tables, it seems that record level auditing
would simplify my work.

2) Are there advantages to auditing in the back end vs. the Access
front end in terms of performance?

3) I have read about using both 'triggers' or 'replication' to create
audits in SQL. Is there a preferred approach?

Thanks in advance for the advice.

[Douglas J. Steele]

If you're using SQL Server as the backend, you should be able to write
Triggers that will automatically log for you after any data change. This has
the advantage that it'll work even if people update the tables directly,
rather than going through your application, and it means no code at all in
your frontend.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Jim M" <ma*****@rci.rutgers.edu> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I've been playing with Allen Browne's audit code and found it very
useful. I need to track record insertions, deletions, and edits for
several tables. I am planning to replace Access with Microsoft SQL
server for my back end, but continue to use Access for the front end.

I understand I can create an audit trail of record changes in SQL at
the table level, instead of at the form level in Access.
I have been playing with Access since the days of 1.0, but an a newbie
with SQL. My application backend was fairly easy to move to SQL -
except for renaming a lot of tables.

My Questions:

1) How difficult is it to implement a record level audit capability in
SQL backend vs. Access.mdb front end? Since I have many forms that
interact with each of my tables, it seems that record level auditing
would simplify my work.

2) Are there advantages to auditing in the back end vs. the Access
front end in terms of performance?

3) I have read about using both 'triggers' or 'replication' to create
audits in SQL. Is there a preferred approach?

Thanks in advance for the advice.

[(PeteCresswell)]

Per Douglas J. Steele:

If you're using SQL Server as the backend, you should be able to write
Triggers that will automatically log for you after any data change. This has
the advantage that

Can the code be made smart enough to:

1) Log only fields that have changed (i.e. not the whole record)
I'm guessing this is probably a no-brainer.

2) Log both 'before' and 'after' values?
I'm not so sure about this one. Knowing it can be done would cause me to
re-think my logging routines on the next project I do.
--
PeteCresswell

[Jim M]

Doug,

Thanks, this sounds interesting. I appreciate the help.

I assume I set up audit tables the same way Allen Browne did in his
audit code, i.e. an audit table for each table I want to audit with all
the same fields plus room for a date/time stamp, user/machine stamp,
and perhaps a new index. I probably also need to flag whether it is
deleted, appended, or edited. I assume the triggers are akin to Access
events (I really am a newbie at this...) Is there any way to directly
pass a parameter to SQL Server from Access, e.g. a value from a form,
or a global variable to write in the audit table? I could write it in a
Backend Table if that is all SQL can read... Thanks again!

Jim

[Douglas J. Steele]

I've only got SQL Server 2000 BOL here, but Example E. Use COLUMNS_UPDATED
in the CREATE TRIGGER entry shows logging the before and after values.

It (and Example F. Use COLUMNS_UPDATED to test more than 8 columns) also
shows how to determine which specific columns have been modified, although
not how to log only the changed columns. However, that shouldn't be that
difficult to code.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"(PeteCresswell)" <[e-mail]> wrote in message
news:c9********************************@4ax.com...

Per Douglas J. Steele:

If you're using SQL Server as the backend, you should be able to write
Triggers that will automatically log for you after any data change. This
has
the advantage that

Can the code be made smart enough to:

1) Log only fields that have changed (i.e. not the whole record)
I'm guessing this is probably a no-brainer.

2) Log both 'before' and 'after' values?
I'm not so sure about this one. Knowing it can be done would cause me
to
re-think my logging routines on the next project I do.
--
PeteCresswell

[Douglas J. Steele]

You'll need to look in SQL Server BOL (Books On-Line) for more information.

A trigger is a special kind of stored procedure that executes automatically
whenever any data-modification statement is used on the specified table. SQL
Server allows the creation of multiple triggers for any given INSERT,
UPDATE, or DELETE statement.

In other words, as long as your Access front-end is correctly updating the
table, SQL Server will take of the logging for you. There should be no need
to pass parameters.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Jim M" <ma*****@rci.rutgers.edu> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Doug,

Thanks, this sounds interesting. I appreciate the help.

I assume I set up audit tables the same way Allen Browne did in his
audit code, i.e. an audit table for each table I want to audit with all
the same fields plus room for a date/time stamp, user/machine stamp,
and perhaps a new index. I probably also need to flag whether it is
deleted, appended, or edited. I assume the triggers are akin to Access
events (I really am a newbie at this...) Is there any way to directly
pass a parameter to SQL Server from Access, e.g. a value from a form,
or a global variable to write in the audit table? I could write it in a
Backend Table if that is all SQL can read... Thanks again!

Jim

[Trevor Best]

(PeteCresswell) wrote:

Per Douglas J. Steele:

If you're using SQL Server as the backend, you should be able to write
Triggers that will automatically log for you after any data change. This has
the advantage that

Can the code be made smart enough to:

1) Log only fields that have changed (i.e. not the whole record)
I'm guessing this is probably a no-brainer.

See Ans. 2 :-)
2) Log both 'before' and 'after' values?
I'm not so sure about this one. Knowing it can be done would cause me to
re-think my logging routines on the next project I do.

Yes, you can use the syntax like:
if update(column)...

There are also two special table names in triggers, inserted and deleted.

In a delete trigger, only deleted contains records (pretty self
explanitory as to what they contain), in an insert trigger, only
inserted has records (again, self explainitory), in an update trigger
they both have records, deleted contains the old data and inserted
contains the new data so you can not only log both but compare both.
--
[OO=00=OO]

[Bri]

Douglas J. Steele wrote:

You'll need to look in SQL Server BOL (Books On-Line) for more information.

A trigger is a special kind of stored procedure that executes automatically
whenever any data-modification statement is used on the specified table. SQL
Server allows the creation of multiple triggers for any given INSERT,
UPDATE, or DELETE statement.

In other words, as long as your Access front-end is correctly updating the
table, SQL Server will take of the logging for you. There should be no need
to pass parameters.

What about logging the User that made the change? The Access UserID is
what I'm refering to. Using TS everyone has the same MachineName. Can
you capture the Access UserID? Or at least the WindowsID used to log
into TS?

--
Bri

[Douglas J. Steele]

"Bri" <no*@here.com> wrote in message
news:_1Bze.1907283$Xk.1405966@pd7tw3no...

Douglas J. Steele wrote:

You'll need to look in SQL Server BOL (Books On-Line) for more
information.

A trigger is a special kind of stored procedure that executes
automatically whenever any data-modification statement is used on the
specified table. SQL Server allows the creation of multiple triggers for
any given INSERT, UPDATE, or DELETE statement.

In other words, as long as your Access front-end is correctly updating
the table, SQL Server will take of the logging for you. There should be
no need to pass parameters.

What about logging the User that made the change? The Access UserID is
what I'm refering to. Using TS everyone has the same MachineName. Can you
capture the Access UserID? Or at least the WindowsID used to log into TS?

SQL Server has at least 4 different User Ids that I'm aware of:
- CURRENT_USER
- SESSION_USER
- SYSTEM_USER
- USER

Afraid I can never remember which is which, but one of them might meet your
needs.

If not, you might try asking in a SQL Server-related newsgroup.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

[(PeteCresswell)]

Per Bri:

What about logging the User that made the change? The Access UserID is
what I'm refering to. Using TS everyone has the same MachineName. Can
you capture the Access UserID? Or at least the WindowsID used to log
into TS?

Long time ago, I used to do that. CurrentUser().

Now I capture the LAN UserID and use that. Much more robust bc it doesn't
force you to use Access security when there's no current need.
--
PeteCresswell

[Trevor Best]

Douglas J. Steele wrote:

"Bri" <no*@here.com> wrote in message
news:_1Bze.1907283$Xk.1405966@pd7tw3no...

Douglas J. Steele wrote:

You'll need to look in SQL Server BOL (Books On-Line) for more
information.

A trigger is a special kind of stored procedure that executes
automatically whenever any data-modification statement is used on the
specified table. SQL Server allows the creation of multiple triggers for
any given INSERT, UPDATE, or DELETE statement.

In other words, as long as your Access front-end is correctly updating
the table, SQL Server will take of the logging for you. There should be
no need to pass parameters.

What about logging the User that made the change? The Access UserID is
what I'm refering to. Using TS everyone has the same MachineName. Can you
capture the Access UserID? Or at least the WindowsID used to log into TS?

SQL Server has at least 4 different User Ids that I'm aware of:
- CURRENT_USER
- SESSION_USER
- SYSTEM_USER
- USER

Afraid I can never remember which is which, but one of them might meet your
needs.

If not, you might try asking in a SQL Server-related newsgroup.

I've not used the first two myself but system_user returns the user name
logged into SQL Server as does USER unless you are the database owner in
which case it returns "dbo"
--
[OO=00=OO]

[Bri]

(PeteCresswell) wrote:

Per Bri:

What about logging the User that made the change? The Access UserID is
what I'm refering to. Using TS everyone has the same MachineName. Can
you capture the Access UserID? Or at least the WindowsID used to log
into TS?

Long time ago, I used to do that. CurrentUser().

Now I capture the LAN UserID and use that. Much more robust bc it doesn't
force you to use Access security when there's no current need.

I use CurrentUser() in my Audit code that I use in my Access Frontends.
After moving the Audit code to Triggers on the SQL BE so that even Table
edits would be caught in the Audit, I would guess that the LAN UserID
(the WindowsID I assume) would be the one to capture. I've done that in
an AC FE before as well using an API call, but I have no idea on how to
make an API call in an SQL Trigger (or SP for that matter). I suppose
that this really is a question for an SQL group, but if you have already
done this, could you post some sample code?

Thanks,
--
Bri

[Trevor Best]

Bri wrote:

I use CurrentUser() in my Audit code that I use in my Access Frontends.
After moving the Audit code to Triggers on the SQL BE so that even Table
edits would be caught in the Audit, I would guess that the LAN UserID
(the WindowsID I assume) would be the one to capture. I've done that in
an AC FE before as well using an API call, but I have no idea on how to
make an API call in an SQL Trigger (or SP for that matter). I suppose
that this really is a question for an SQL group, but if you have already
done this, could you post some sample code?

SQL Server only knows the name you logged into SQL Server as, if you use
Windows Integrated Authentication then the system_user function will
return that in the form of domainname\username.

--
[OO=00=OO]

[Bri]

Trevor Best wrote:

Bri wrote:

I use CurrentUser() in my Audit code that I use in my Access
Frontends. After moving the Audit code to Triggers on the SQL BE so
that even Table edits would be caught in the Audit, I would guess that
the LAN UserID (the WindowsID I assume) would be the one to capture.
I've done that in an AC FE before as well using an API call, but I
have no idea on how to make an API call in an SQL Trigger (or SP for
that matter). I suppose that this really is a question for an SQL
group, but if you have already done this, could you post some sample
code?

SQL Server only knows the name you logged into SQL Server as, if you use
Windows Integrated Authentication then the system_user function will
return that in the form of domainname\username.

So far I have been using a common Login to SQL and controlling security
through Access Security on the Linked Tables. I realize this isn't as
secure as using the Windows ID (since someone could figure out the
common ID and then have all access) but it is enough for my current
purposes. I guess I'll have to keep my Audit code in the FE for now.

--
Bri