MS Access Audit Trail

[SUnderwood]

Dear All,

I need to implement an audit trail mechanism into an Access 97 database, a function I know is not native to its operation. I've been delaying the idea for a few months because of the MAJOR recoding I envisage to implement it.

The audit-trail need only be simple, for each field in a record showing its previous value and its new value, the date and by whom it was requested to be stored in a string field, in say a table called [audit].

If anyone has experience implementing such a beast I'd be grateful to hear your suggestions.

Many thanks

Sean Underwood

 

[Prof63]

Sean

I'm currently undergoing the very same thing myself. I've got the users stored in a table as they access the DB, all I need to implement now is the date, time, old field value and new field value! I'll post the salient bits of the code when I'm done.

 

[SUnderwood]

Prof

Thanks for the support. I've searched the Tek-Tips forums for previous topics relating to audit trails and found thread181-122044 contained some usful discussions and good links to previous ideas for audit trail coding (faq181-291 titled transaction log is a good one).

 

[JeremyNYC]

Don't sweat too much yet...it's been done before. I think MichaelRed once posted some really good code for this (it might even be in a faq he's got in his profile). I know there's some code to do it in the knowledge base on microsoft's site.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[RobPotts]

Hi,

see also

FAQ181-2399

This works for me and it would record all the info you are looking for

Rob! [Bigcheeks]

 

[SUnderwood]

rob,

Thanks rob. Neat code, easily understood and adaptable to other requirements.

I'm going to search the Microsoft website today and report back my findings.

Regards

Sean (-:

 

[MichaelRed]

Always interested in 'new' approaches, I checked the faq reference to see what they were doing that I did not cover. With an admittedly quick look, thier code doesn't look like it covers as much as mine. Specifically, there is no provision for capturing memo fields.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SUnderwood]

Hello All,

As promised I found the following on the MS website: "Q183792 & Q197592 - How to Create an Audit Trail of Record Changes in a Form"

This routine works by keeping the audit details in a field called [Updates] which is added to the table to be audited. Easy to see how a current record was changed, but
1) makes it difficult to delete audit trails,
2) audits are part of the record not related to it,
3) Code is native to the form and coding must be present in all forms to be audited.
Still a great that it audits.

Thinking more about the problem I would imagine the following would make a good audit table

AuditTrail {
PK[DateTime] DateTime Default Value '=GetDateTime()'
PK[UserName] string(*) Default Value '=GetUserName()'
PK[TableName] String(*) Required = TRUE
[Changes] Memo Required = TRUE
}

GetUserName(), if not already defined, should be declared in your favourite way.
GetDateTime() should use a client independant means of obtaining correct DateTime.

The audit function could have the following signature:

AuditMe(ByRef MyForm as Form)
Precondition: MyForm is the active an access form
Postcondition: If MyForm has updated controls, an extry is made in [AuditTrail] containing old and new values for the underlying table [TableName]. If an error occurs the user is notified and the trail not updated.

My next question is when should this routine be called? Access does not conform to the ACID test so this function must be called after we are sure the data has been committed to disk - or disk cache at least. Therefore, BeforeUpdate is not good enough. AfterUpdate or AfterInsert may be better.

Any thoughts on the above?

Regards

Sean

 

[MichaelRed]

See the reference (faq181-291). Even if you don't like it, it already has 90% of what you note and 15% you missed.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SUnderwood]

Thanks JeremyNYC, MichaelRed

I have tested the routine in FAQ181-291 which holds the most promise of all routines found, in fact its the best. Whats more interesting about this particular routine is that it can be used to recover lost data, however at the expense of a much larger log table (every altered fields has its own record)

I found the benefits to be:
1) It recorded all changes to controls with additional fields needed to existsing tables
2) An Undo command could be made using the [tblHist] table
3) Routine easily inserted into existsing forms

I found the main problems to be:
1) The code was slightly incorrect,
2) It used two tables to store the audit trail - distinguishing between memo and other fields,
3) It seemed to use Now() as a key field (Now() changes with every second and an update to the transaction table may take more than a second)

All routines failed to record the deletion of a record.

I also noted that all routines committed data to the transaction table before updating the data itself, using the BeforeUpdate event. The AfterUpdate event should have been used to allow Access to function more closely to the ACID test. But maybe there are internal Access mechanisms that make BeforeUpdate a better option. So my next set of questions would be:

a) How can one ensure the data has been comitted to disk,
b) Is there a reason why BeforeUpdate has been used rather than AfterUpdate?

Regards

Sean

 

[MichaelRed]

A few responses:

Delete was not part of the specification under which the routines were developed. No "user" had access to the data except through forms, and delete was not allowed. the system was intended as a perpetual process. I did implement an archive, which moved records older than ? (I recall a year and which had been completed) to a different db, however the search process was also modified to have hte option of including the archived records in the search process. A small addional routine could easily be addded to include deletions.

Use of two tables was a choice. To capture MEMO fields in the same manner (as numeric and smaller text) would have required that all data fields to be of memo type, or the omission of the memo fields in the db. Neither of htese were acceptable.

I'm not certain, as it has been a WHILE since I actually implemented the proceures, but "Now" doesn't ring any bells for primary key. In any case, "Now" is actually accurate to somewhat more than one second, it is just the display format which is restricted to the one second. If I recall it correctly, "Now" is updated on each 'tick' of the system clock (i/18 of a second). In any case, some manner of ordering the log is necessary for even meaningful review, much less a viable recreation process.

You note that there are some errors in the code. Tek-tips provides a mechanisim to provide feedback directy to the author on any such issues, and I would urge you to avail yourself of the opportunity and attempt to get the author to fix any problems to the benefit of yourself (and other potential future users).

My understanding of the before - after issue is simply the chicken and the egg issue of which cam (or should come) first. If you save the audit info first and update the record later, it is possible to record the audit of a transaction which did NOT actually occur, but it is at least clear that it was intended. If you save the audit record(s) after the transaction, it is possible to record the transacction - but NOT have audit record(s) for the transaction - which ever sequence you prefer, a catastrophic failure at some point can leave the system in an inconsistient state. I know of no way to have the pocesses occur simultaneously, and the overall thought process convince me that additional efforts to assure concordance would introduce more overhead than warranted.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RickSpr]

Regarding the BeforeUpdate/AfterUpdate choice, I concur with MichaelRed that with either one there is a risk of inconsistency. However, it's possible with either one to improve the chances that the audit trail remains consistent, by using the function return value from basLogTrans.

If the function returns False, display a message notifying the user that the transaction could not be completed (because the audit trail update failed). If the call was made from BeforeUpdate, set the Cancel parameter to True, otherwise execute RunCommand acCmdUndo.

There's still a slight chance that things will get out of sync. If using BeforeUpdate, it's possible that the form update will fail (e.g., a required foreign key may be Null). If using AfterUpdate, it's possible that the Undo will fail. The latter seems less likely to me, so I'd use AfterUpdate.

Michael, I think the error SUnderwood is referring to is that basActiveCtrl returns True for some control types that it shouldn't. For instance, Label, Rectangle, Line and Image controls have no Value or OldValue property, but your code would attempt to record them. Likewise, OptionButtons and CheckBoxes have no Value or OldValue if they're in an OptionGroup. basActiveCtrl should check the Parent property for these to ensure it's Null, before returning True. I'm not sure about BoundObjectFrame controls.

One other thing: As you pointed out, this code only works for bound forms, meaning forms in which all the data controls are bound. As an enhancement, you could check the ControlSource property of each control for either a zero-length string or a string starting with "=", and skip that control if true. That would enable this code to be used on forms where some controls are unbound, or are bound to an expression. (Such controls don't have an OldValue property.) You included a comment about checking ControlSource at the point where this should be done; perhaps this is what you meant. Rick Sprague

 

[MichaelRed]

rickspr

Thanks for your review and commentary. I have previously considered the use of the ControlSource property of individual controls fo use in checking wheather the control's value should be included in the history. For the original use, I was able to do without this check, but I do agree that it would be a useful addition if the procedure set were to be more generalized.

On the matter of which controls are noted as "active" (e.g. baActiveControl), the procedure -at least as posted- only returns TRUE for four (4) control types:

[tab]ChckBox
[tab]TextBox
[tab]ListBox
[tab]ComboBox

So you thought re returning more (e.g. Label, line, Rectangle, etc.) true values is not technically correct, but may be an issue for some users. I deliberatly listed (all of) the control constants in the selet case construct to provide and imply some degree of flexibility in this matter.

I do not see this as an "error", so assume the reference to "errors" in the earlier posting to arise from other issues -which I fail to see. The routines were originally written in Ms. A. '97 without the use of any custom controls (partly because I am Soooooooooo right wing, and partly because the end-user was less than grenerous in the arena of third party tools). So, it appears (to me) that the error (if any) in this area is on the opposit tack - that they have controls which are bound to a control source which do NOT return TRUe from this function. After all, in this approach, the user would need to identify each control which is bound and include it's type in the select case structure -and set the return value.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RickSpr]

Michael,

We don't seem to be looking at the same code. Here's a cut and paste from faq181-291:

Public Function basActiveCtrl(Ctl As Control) As Boolean
    Select Case Ctl.ControlType
        Case Is = acLabel
        Case Is = acRectangle
        Case Is = acLine
        Case Is = acImage
        Case Is = acCommandButton
        Case Is = acOptionButton
        Case Is = acCheckBox
            basActiveCtrl = True
        Case Is = acOptionGroup
        Case Is = acBoundObjectFrame
        Case Is = acTextBox
            basActiveCtrl = True
        Case Is = acListBox
            basActiveCtrl = True
        Case Is = acComboBox
            basActiveCtrl = True
        Case Is = acSubform
        Case Is = acObjectFrame
        Case Is = acPageBreak
        Case Is = acPage
        Case Is = acCustomControl
        Case Is = acToggleButton
        Case Is = acTabCtl
    End Select
End Function

Are you saying that this would return False for a Rectangle? I can't believe this is the code you're looking at. Rick Sprague

 

[MichaelRed]

Hmmmmmmmmmm,

Since you dont 'believe' it, I can only suggest that you try it.

On a purely academic level, WHAT, in the code would may you 'believe' it would return 'true' for a rectangle? I only see the four controls where the return value is set to true. Since the default refurn is FALSE, how do any which are not explicitly set get to the TRUE?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RickSpr]

Michael, my apologies. I've made this mistake before.

In the C equivalent of a Case statement ("switch&quot, you can't list multiple selectors together (as in "case 'a', 'b':&quot. Instead, you list them one after another ("case 'a':" followed by "case 'b':&quot, and the absence of an executable statement following one selector indicates that it selects the same executable statement as the selector after it.

Every once in a while, seemingly at random, I apply this interpretation to VB Case statements, which is what I did here. I've made a habit of simply not listing no-action cases in VB, in order to avoid confusing myself, but that just makes it harder to remember what they do when I encounter them.

Again, my apologies for taking you on a wild goose chase, and thanks for the good FAQ. Rick Sprague

 

[SUnderwood]

I did not intend to flame you, MichaelRed. The incorrect code I was refering to is in basAddHist

!MyKey - Should have been !KeyVal
!MyKeyName - Does not exist
!frmName - OK
!FldName - OK
!dtChg - Should have been !dtChanged
!UserId - OK
!OldVal - Should have been !OldContent
!NewVal - Should have been !NewContent

These are the changes I had to make for the code to function correctly. Apart from that, as I said, its the best code I've seen!

If i have misunderstood the field names I would like to know.

Regarding the BeforeUpdate/AfterUpdate choice, I am leading toward thinking the AfterUpdate option is the better. The main reason for a transaction log is to return the database to a consistant state after an error. Putting the transaction routines in the AfterUpdate would ensure the transaction log is updated only when the database moves from one consistant state to another.

If a transaction log commit fails only the transaction log will be corrupt leaving the database in a consistant state. If this happens, the database should be backedup and a new transaction log started.

Any thoughts?

Sean

 

[MichaelRed]

I did fail to update the embedded ddocumentation of the history table structures. Thnk you for pointing out this.

re the question of wheather to use Before or After, as I have already stated, I think of this as a matter of choice, with little to no difference overall. Additional efforts to assure the process is 'perfect' for Ms. Access dbs are probably -in my opinion- not worth (cost/benefit ratio) the additional complexity. If any useful attempt were to be made, I would think that wrapping the entire process in a transaction grouping would be a necessary part of that effort. This transaction block would need to be external to the code which I have posted, so inclusion (in these functions) is inappropiate, with the possible exception of additional "commentary". This (conmmentary), as you have already noted is not my strong suit, making it doubly difficult for me to have an interest in it. Further, Since I am currently un-employed, I have little opportunity to set up and test the process in any meaningful manner, so I will leave that process and documentation to those in a better position to evaluate the results.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RickSpr]

Michael,

Unfortunately, I don't think the transaction idea can work. I tried to do this once (BeginTrans at Form_Open), and learned that updates done via the form's recordset do not participate in the transaction. I tried to trace it out, and I'm not 100% sure I got it all right, but what I concluded is that the recordsets opened for forms (and, I presume, reports) are opened not only in a different Workspace, but on a different, private DBEngine!

What that means, of course, is that if you're going to do any updating of your own behind a form, you have to do ALL the updating in code, or you learn to live without 100% update integrity, or you do it in something besides Access.

Wouldn't you think Microsoft would have mentioned this fact, somewhere in the help file? I guess they thought it would be bad publicity to admit that Access applications can not have reliable data integrity. Rick Sprague

 

[MichaelRed]

Hmmmmmmmmmm,

I (obviously?) never tried this. I would agree that is is at least a bummer. Off hand (top of head not found), I would look at 'repackaging' the transaction to just put BOTH of them in the audit process. After all, all of the fields and values are (already) available, and it should be trivial to capture the forms' recordsource, so while it WOULD be weird, one could construct the record similar to the method used in the audit, setting each field in the recordsource to the controlsource. Weirdness occurs when the record is saved -in the audit function- but the Before/after update process which calls the audit now needs to CANCEL the record operation. Only new part I would see is the necessity of determining wheather this is an [add | edit | delete] operation for the record. This would (except the weirdness mentioned) perhaps be more inline with the more standard approach?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places