Tracking changes/edits made by users on records.

[Shem K]

Hi all.

Thank you, NeoPa, for your kind assistance in my previous thread http://bytes.com/topic/access/answer...rm#post3790900
and to Z for highlighting the macro image in it.

...
My database is nearly done, before I release it to the users for the pilot implementation phase (while I distribute respective user front-ends).

I would want to create a system that:

1. Shows me who accessed a certain record, when last it was accessed and what time - like tracking changes to a record (For Admins. May require a bit of user authentication, which I am a baby in in terms of access security
2. Notifies a user who is currently accessing a record, like a pop-up window (I know this might be distracting, hence annoying at some point, especially when someone is receiving two or three simultaneous pop-ups while working on screen.)

This is kind of a monitoring system (Having, for example, one front-end monitoring database activities of several other front-ends).

I know I need an entire tutorial on this one (I'm skimming through the Access/VBA section on Bytes), and hope I can pull it through in the course of pilot.

Working with Access '07. Still green in VBA though.

Thanks in advance.

S

[computerfox]

For each record, you can add LAST_VIEWED_BY and LAST_VIEWED_DAT E columns
For the changes, you should make a history table which includes RECORD_ID,ORIGI NAL_TEXT, NEW_TEXT,LAST_U PDATED_BY,LAST_ UPDATED_DATE columns.

Hope that helps.

[jforbes]

There are quite a few ways to accomplish what you want to do. Which ways you use will be determined on how far you really need to take your logging and how much code you are willing to write, and how much space you are willing to devote to the possible massive amount of data you could generate.

But first off, if this is a requirement that would be "nice to have", I would recommend you try to talk whoever wants this out of wanting it. This is a large undertaking so make sure you really need it:

• There will be quite a bit of code to write and maintain. And this code can easily end up scattered through out your application, complicating maintenance. If you are still pretty new to VBA, this could be a bit too much to tackle.
• Although the latest version has made some in-roads towards this functionality, Access wasn't designed for this as a Desktop Database. Where as SQL Server has easy ways of accomplishing this, being a true multi-user environment.
• You could be generating a considerable amount of data that will never be used.
• Any reliance on Access' security is misplaced as there is almost always a way around the security.

If all these scary warnings haven't deterred you, let us know and we will either expand on what computerfox has mentioned or get more specific about what you need.

[zmbd]

J is absolutely correct.

Any reasonably advanced user can bypass most if not all of the security measures taken by the access programmer.

With that said; however, Allen Browne has a fairly comprehensive article with working code/examples that many of us have used over the years. Creating an Audit Log

Create your front-end/back-end.
Secure both as well as be done with passwords and encryption.
Secure your VBA project and protect from viewing in the front-end.
Create the executable version of your front-end using a copy of the front-end file (this is not a reversible process so make sure you keep a development copy!).

[Shem K]

Computerfox, thanks for running me through the fields and history table I need to include in my system.

Jforbes, thanks for giving me an eagle's eye view into what I'm treading into. The organisation for whom I'm developing the database handles client files in the region of 1.5-2k or 2.5k+. This means that in the course of additions, deletions and edits, the history table as suggested by computerfox will be highly populated, noting that changes to each file are made on a daily to weekly basis on average (say, nearly 50 to 80+ files in a day, depending on the work updates on them). As you've correctly pointed out, that's a red flag for space consumption on the database. Seeking your indulgence further: on assumption that this goes through, it means there needs to be an auto-delete system, that clears the changes made on the history table, periodically, say weekly or monthly. Then again, this means that in case a user intends to view changes to a matter made like two months ago, they wouldn't find them - unless, of course, another setting is included to manually delete change made on the history table from the form level, which I think is another daunting task altogether.

Z, I've read through and internalised Allen Browne's article on Creating an Audit Log. Noting that the users are pooling into one database (hence history table), and that each will be supplied with their respective front-ends with passwords, it means that, ultimately, what Jforbes has elaborated on would render more harm than good in terms of efficiency in the main purpose of the database.

Alternatively,
On the inclusion of the Last_viewed_by and Last_viewed_dat e fields:
Last_viewed_by: registers the username of the user/workstation who last viewed the file. It means that code is needed to capture details of the user/workstation?
Last_viewed_dat e: (possibly both date and time) automatically entered/altered/updated upon an alteration/update of the Last_viewed_by field. It means it must be dependent on the change at Last_viewed_by (a BeforeUpdate event or AfterUpdate event?).
..such that, when a change is made in the main record on the form, the Last_viewed_by and Last_viewed_dat e fields are automatically updated.
(Z, do I need to start another thread on this one?)

[zmbd]

Shem_K: at this point, so long as we stay on the topic of creating the audit log we're good with this thread and branch later if needed.

One thing I do with my audit log is use a second password protected backend so that almost the full 2GB is available, that's a very large number of record entries, and each year I have code that starts a new audit log backend. We don't have a lot of changes so this works well. Given that you appear to not want a lot of detail about changes this may work very well for your application.

Obtaining usernames... http://bytes.com/topic/access/answer...ogged-username.

Capturing the date/time is trivial using the NOW() or other date functions.

Reading thru your original post... my thought here is to use your email system for your monitoring system......

[Shem K]

Hi guys. I needed some time off this one to digest content. Had a rather busy week too working on developments from other queries I raised on Bytes.

That's a very ingenious way of starting a new audit log backend. I would be willing to have a go at it.

I went through the directions given by Allen Browne in generating an Audit Log. I generated the two tables: tempAudit and Audit tables. I also created an audit module (I called it Module Audit), copied Allen's code in it, and made necessary changes to reflect the fields in my database. I then sampled the codes in one Form at the required Events (OnDelete, AfterDeleteConf irm, BeforeUpdate, and AfterUpdate). I tested out a data edit, and this prompt appears:

Run-time Error '13':
Type mismatch

When I prompted to Debug, the debugger highlighted the third line on this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
bWasNewRecord = Me.NewRecord
Call AuditEditBegin("List_of_Matters", "List_of_Matters_Audit_temp", "File_Ref", Nz(Me.File_Ref, 0), bWasNewRecord)

End Sub

(I know I'm trying to swallow a mouthful - something beyond my VB knowledge - but I'm willing to give it a go)

[zmbd]

Allen's Funcution:

Function AuditEditBegin _
   (sTable As String, _
    sAudTmpTable As String, _
    sKeyField As String, _
    lngKeyValue As Long, _
    bWasNewRecord As Boolean) As Boolean

Your call:

Call AuditEditBegin
   ("List_of_Matters", 
   "List_of_Matters_Audit_temp", 
   "File_Ref", 
   Nz(Me.File_Ref,0), 
   bWasNewRecord)

1) me.file_ref most likely refers to a bound text field; however, is the value in that field a string/text or a numeric?

2) bWasNewRecord isn't explicitly declared as the Boolean data type Dim bWasNewRecord As Boolean in your code. MS changed how undeclared variables are handled in ACC2007/2010(+)-VBA and that has caused some of my older code where I didn't declare (yes, I know, always declare one's variables - lazier back then) the variable datatype to fail with type mismatch. Thus, I suggest that first:

1.Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim bWasNewRecord As Boolean
2. bWasNewRecord = Me.NewRecord
3. Call AuditEditBegin("List_of_Matters", "List_of_Matters_Audit_temp", "File_Ref", Nz(Me.File_Ref, 0), bWasNewRecord)
4. 
5. End Sub

[Shem K]

1) Me.File_Ref is a text field (specifically, a Memo field).

2) I've declared the Boolean data type as you've suggested above. Still, the Type mismatch appears at my Call AuditEditBegin line.

[zmbd]

1) Me.File_Ref is a text field (specifically, a Memo field).

and there's most likely your problem as the pass thru
lngKeyValue As Long requires a long cast type.

So workaround, assuming that the value in Me.File_Ref is numeric, is to use the CLNG() type conversion

CLNG(Nz(Me.File_Ref,0))

(BTW: Memo fields are notorious for being bad actors in Access... good for a lot of things and difficult to handle in either Macro or VBA when one needs to actually use the data :-( )

[Shem K]

Me.File_Ref has alphanumeric data based on the filing system, and it uniquely identifies all other fields in the record.
I've changed its data type to Text instead of Memo on the table's design view. I've also included the CLNG () type conversion, but I'm getting the same error message.

[zmbd]

SK
Sorry for the delay.
The issue is the alpha part of the numeric.
If you will take a close look at the function, the passed variable must be cast as the long-numeric. Alpha-Numeric will not work for this function.

[Shem K]

Hi Z. I trust your weekend was great.
Wow. I didn't know that. I guess I'll need to omit the code. Had hoped that it would include alphanumeric values too. Thank you for taking me through it, though. I'll keep this code to use in case I land another Access/VBA-based assignment.

[zmbd]

you might consider adding an autonumber field, this is just right up its alley...

[Shem K]

I've included an Autonumber field (field name File_ID) into my List_of_Matters table, and reflected it into my sample form and respective form events for auditing. After compiling and testing data, I get this error:

Run-time error '3078'
The Microsoft Access database engine cannot find the input table or query 'List_of_Matter s_Audit_temp'. Make sure it exists and that its name is spelled correctly.

(List_of_Matter s_Audit_temp is my Audtemp table)
When I prompt Debug, my Audit Log module is opened and the db.Execute sSQL is highlighted (fourth line below):

 'Remove any cancelled update still in the tmp table.
Set db = DBEngine(0)(0)
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL

I can't pinpoint why my Audtmp table can't be found.