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_DATE columns
For the changes, you should make a history table which includes RECORD_ID,ORIGINAL_TEXT, NEW_TEXT,LAST_UPDATED_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_date 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_date: (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_date 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, AfterDeleteConfirm, 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_Matters_Audit_temp'. Make sure it exists and that its name is spelled correctly.

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

[NeoPa]

Hi Shem.

Can you show us the value of sSQL as the code stops on that fourth line please. That should give us a clue or two.

Basically, we need (you) to check that the reference to the table is valid in two ways :

1. The table as included in the SQL exists.
2. The name of the table is valid in itself and doesn't need to be surrounded by '[' & ']' characters.

You'd be looking for spaces in the name or such like.

[Shem K]

Hi NeoPa.
Please see from line 8.
I included the previous code that relates to it:

    'Remove any cancelled update still in the tmp table.
    Set db = DBEngine(0)(0)
    sSQL = "DELETE FROM " & sAudTmpTable & ";"
    db.Execute sSQL
 
    'If this was not a new record, save the old values.
    If Not bWasNewRecord Then
        sSQL = "INSERT INTO " & sAudTmpTable & " ( Audit_Type, Audit_Date, Audit_User ) " & _
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
        db.Execute sSQL, dbFailOnError
    End If
    AuditEditBegin = True
 

[NeoPa]

I'm thoroughly confused as to why you'd want me to look at that code. It doesn't seem to follow on from what I suggested you do in any discernable way.

I did look at the code just in case it could help. It answered no questions and gave no useful information that I was able to see.

Maybe we should try again on this one. Please read my earlier post again and :
A) Show the value requested.
B) Confirm that the table does exist and is spelled exactly as you have it in the value which you will (hopefully) have shown by then.

NB. It's the SQL code we're looking for here. Not any VBA code. VBA code that creates a SQL string is not a SQL string value. I hope what I'm saying is clearer this time.

[Shem K]

Sorry. I thought it was the one in the VBA.
It's clearer now. Navigating to the sSQL code is all new to me. I'll kindly need your help on doing it please, especially in unraveling that statement

db.Execute sSQL

, as, in light of what you have just pointed out, I don't know which SQL it's referring to.

Again, I apologise for my newbieness :(

[zmbd]

insert right after the sSQL variable the following:

Debug.Print sSQL 

Press <Ctrl><G>
Run your code.
In the window that appears in the VBAISE when you pressed <Ctrl><G> the resolved string value for sSQL at that point will be printed. That is the string we need to review.

[Shem K]

Hi guys. I trust you had a great weekend.
Hi Z. The string value on the Immediate window (Ctrl+G) is:

DELETE FROM List_of_Matters_Audit_temp;

[zmbd]

Line 3

  sSQL = "DELETE * FROM " & sAudTmpTable & ";"

:)

[NeoPa]

Shem:
The string value on the Immediate window (Ctrl+G) is:

DELETE FROM List_of_Matters_Audit_temp;

Perfect. That's exactly what we were after. Thanks for stepping in Z.

ZMBD:

sSQL = "DELETE * FROM " & sAudTmpTable & ";"

In fact, neither the asterix (*) nor the semi-colon (;) is necessary for this SQL to work correctly.

NeoPa:
B) Confirm that the table does exist and is spelled exactly as you have it in the value which you will (hopefully) have shown by then.

@Shem.
Your SQL looks fine. What you still need to tell us from my post #16 is whether that table name is valid in your database at the time it's run. The error message in post #15 indicates that it is not. Please check the spelling very carefully as it's not a syntax problem.

[Shem K]

Hi NeoPa. The table does exist in my database. I did counter-checked the spelling severally, and the name is intact.

I thought it was probably the underscore present in my call events that made the code not recognise the table. I removed the underscores (to read "List of Matters Audit temp" in place of "List_of_Matters_Audit_temp"), but this brings a syntax error.

Run-time error '3131':
Syntax error in FROM clause.

The error is on line 3.

I'd say I'm stuck at this point.

[zmbd]

bad habit that with asterix and semi-colon :)

[zmbd]

if the table name has spaces then you have to enclose it in the brackets nor will the underscore substitute for the space so the table must be comme ça:
[List of Matters Audit temp]

1.sSQL = _
   "DELETE * FROM [" & _
       sAudTmpTable & "];"

:) yes the "*" and ";" ... old dog - new tricks :)

[jforbes]

When getting stuck like this and you have the SQL Command in the Immediate Windows, you can open a new Query, switch to SQL, paste the SQL from the Immediate Window into the Query and attempt to run it. You should get the same error with the possibility of Access highlighting what it thinks is wrong.

[NeoPa]

The asterix and the semi-colon won't hurt at all. On the other hand their absense wouldn't explain the problem being experienced.

JForbes' idea of creating a QueryDef object based on the SQL you're trying to execute is a good one. One can often get better and more informnative error messages from a QueryDef that can help point you in the right direction to look for the problem.

That may be your best approach as I see nothing wrong with what you already have. Typically that error message that you got earlier is a strong indication that it doesn't recognise the table specified in the SQL. Whether that's due to spelling or incorrect specificatioon (A table name with spaces in which isn't surrounded by [] characters for instance.) is not clear.

If the trick JForbes suggests doesn't help you then you are left trying to determine why Access doesn't like tha table name supplied, because it seems clear it doesn't.