Audit Trail 1

[nettie205]

I created a database that has all our security information from our DB2 system. I have 6 main tables with 4 association tables. I have populated all the tables using surrogate ID's as the primary key(PK) in each of the 6 main tables and then made the association table with its own surrogate primary key with the 2 foreign keys(FK) included from the main tables.
Example:
Table 1 = Bind_Owner
BNDOWNR_ID (PK)
BNDOWNR_NM
BNDOWNR_DESC
SYSTEM
ENVIRONMENT

Table 2 = Application_Files
APP_FILE_ID (PK)
NAME
CLASS
SYSTEM
ENVIRONMENT
ACCESS_SELECT (Y/N)
ACCESS_INSERT (Y/N)
ACCESS_UPDATE (Y/N)
ACCESS_DELETE (Y/N)

Table 3 = BINDOWNR_APP_ASSC
BNDOWNR_APP_ID (PK)
BNDOWNR_ID (FK)
APP_FILE_ID (FK)

I need help in creating something that will track updates, additions, deletions to these tables by the programmers in the agency. I need to develop an audit trail within this system.

I can't visualize an audit trail system and how to set this up. How do I capture the changes and user information to send to a table?
Please advise
Nettie205

 

[Ymesei]

Nettie,

Esentially, what you would need to do is create some code behind every action that you want recorded that would append a record to your audit tables accordingly. This can be done by writing an append query that would fire when an action is performed. Obviously, a specific query would need to be written for each specific action considering that different actions would require different data to be updated.

If you want to record the user who performed the action, it would be easiest to have a field open with the user's name in it on some form. I would suggest having a "logon" form open upon startup of the database. When the user logs on, a form can be opened that would hold the user's username until the database is closed. This form would be protected so the user cannot change the username in the field (for control purposes). You could even setup a macro that would "logoff" the user and prompt for a new username.

HTH

Will

 

[nettie205]

Thankf for you help. I will try this out and let you know how this works.

 

[SgtJarrow]

nettie205,

I have asample database witht his all setup so you can see how to do it. It has an auditor that you can customize with your own event d everything. Just send an email to robert.l.johnson.iii@citigroup.com and I will forward it to you. The secret to creativity is knowing how to hide your sources. - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[nettie205]

You are an angel. Thank you so much for your quick response. I will look at the code.