Audit Update of a Field

[Pat Hartman]

Do you have use cases for the audit log? If you don't know what you want out of it, you won't know what to store. The audit log is more like a data warehouse than a transactional database. Given that, the easiest way to process it is to flatten it totally. That means you save not only the changed field but ALL fields AND you include the lookup values for all foreign keys. That gets tricky. Just how many columns from the related table do you have to copy and if related tables have related tables, how far do you have to go? You will almost certainly exceed the 255 column limit for tables and queries.

Most of the Audit log samples either log ONLY one column per row or they log the entire before and after row contents but don't flatten the foreign key data.

You need to think long and hard about what outputs you need from the logged data.

When I create audit logs, the purpose is generally to track the changes to a particular column and who made the change and when. There is no requirement to reconstruct an entire record or display the data in context.

 

[Marshall Brooks]

I can probably figure out the rest of the issues with a co-worker who is familiar with SQL.

 

[jdraw]

A few things to consider:
-you seemed adamant that you only needed basic info for after update--now this has changed??

-it is time to get clarity on what exactly is the purpose of/for your audit table. This should be related to business needs/requirement. What is required is not determined by the co-worker familiar with SQL. The SQL person may help with HOW the WHAT gets implemented.

-if you have an authoritative table that relates user login info to actual names, then you can that in combination with your GetUserName type of function to get the value (whatever you need--research the requirement) to record in the tblAuditLog.

-have you tried/tested using an update query to modify a record(s) in your tblSource. You'll find that such a change is recorded in the tblAuditLog.

 

[Marshall Brooks]

@jdraw - Basically all of your points have been considered ... (And fortunately, we are early on in the process ...)

-you seemed adamant that you only needed basic info for after update--now this has changed??

No, it hasn't changed. As I said initially, the need/requirement is for audit info when a field changes. The want/desire would be nice to have a record in the table when a record is added or deleted - with or without a field change. (Partly b/c we have had previous issues with records disappearing and/or being copied over. I have both working now with your help.)

-it is time to get clarity on what exactly is the purpose of/for your audit table.

Essentially, we have date fields in the database for completion steps in our process. We don't have information on Who entered the data for the completion steps or when the data was entered. We have PDF files that are signed and retained separately from the database to also track the completion steps. The idea is get rid of the PDF files and use the database to track/record/audit the completion steps.

What is required is not determined by the co-worker familiar with SQL. The SQL person may help with HOW the WHAT gets implemented.

I never said anything otherwise. The co-worker speaks SQL. I speak VBA. The main difference is with the co-worker, I can say "Here's my database - how would I do this?" And as @Minty said, he basically said "Well, you could go with a left Join in your WHERE statement in your SQL query", which went somewhat over my head. The advantage of the co-worker over asking on here, is I don't have to say "I have a table, but it's really called something else, and it has these fields, but they aren't the real names, and I want to do this, but I don't know how, so when you reply back, I'll convert the created names for the real names and hopefully it will work."

-if you have an authoritative table that relates user login info to actual names, then you can that in combination with your GetUserName type of function to get the value (whatever you need--research the requirement) to record in the tblAuditLog.

Yes, I found a glitch in the matrix this morning and two possible workarounds. I have an authoritative table that relates CURRENT login info to actual names. I usually remove people from the table when they leave the company/department. So let's say Joe Smith is ab12345. He leaves the company. If the table pulls from my lookup table, the tblAudit says Joe Smith made the change 5 years ago. Okay, he was here then, makes sense. If the record is gone from my lookup table, the tblAudit says ab12345 made the change and nobody knows who that is, and if he left the company, he won't show up in any of our global searches either. HR should have a record that they could cross-reference, but I wouldn't want to tell an auditor "I think ab12345 is Joe Smith, but he's not working here anymore, I'll have to verify with HR and get back to you on that."

Workaround 1 is to record the real name in the tblauditresults - which probably means copying Elookup to the BE, but avoids all the SQL Join query issues.

Workaround 2 is to never delete entries from the table, but add an Active Employee Yes/No field and uncheck it when someone leaves.

-have you tried/tested using an update query to modify a record(s) in your tblSource. You'll find that such a change is recorded in the tblAuditLog.

We rarely do that, but one of the main reasons I wanted to go with DM for this is that often the fields might technically be updated via VBA rather than direct input by the user.

Thank you again for all of your assistance!

 

[jdraw]

Good stuff.
I would put an inactive flag on the record of the user who has left the company. That keeps the data in the database rather than tracking down an employeenumber and matching to a user name via HR or a pdf file.

I agree with your comment re the SQL guy. My concern with the What to record in the tblAuditLog was to get management or whoever has to use the information to identify the requirement. Someone(s) reference the data to make some decision or correction --who are they and what do they need. If you can identify or mock up how the data is used, it will help with designing your tblAuditlog and the related macros to capture the data. Eventually you can use the tblAuditlog to create queries or reports to satisfy specific requests. But it is the user of that data that can describe what needs to be captured.

 

[Marshall Brooks]

Correct - as I said initially, I might get this working perfectly and management may say "That is not acceptable, you can't do that." Management (local) is aware of that risk and has authorized me to try it anyway. (And none of this gets RELEASED until management okays it. Worst case, we keep (and modify) the current process.

 

[Minty]

I usually remove people from the table when they leave the company/department.

Don't remove them - simply add a DateLeft field and fill it out.
Then your records will always match up for ever.
You can filter out those who have left from new data entry by excluding anyone with a DateLeft that isn't null.

"I have a table, but it's really called something else, and it has these fields, but they aren't the real names, and I want to do this, but I don't know how, so when you reply back, I'll convert the created names for the real names and hopefully it will work

Why do this - unless your table and field names are called something daft like MyCompanyName.FredSmith they are just field names, not confidential material? They might mean something but surely it can't be that sensitive?

 

[Marshall Brooks]

Why do this - unless your table and field names are called something daft like MyCompanyName.FredSmith they are just field names, not confidential material? They might mean something but surely it can't be that sensitive?

Self-protection. It isn't that the information is that sensitive in itself. It's that if I ever get questioned, I can say "This is what I asked and it is all generic information."

If the right people get involved, it would come down to: Did your management know and approve of you asking these questions? (Yes, local management, although they don't know everything I post...) Who above local management was aware of and approved this exchange? (Now my immediate manager is in trouble) Did you have a non-disclosure agreement and a third-party proprietary authorization agreement with everyone you posted this information to and everyone who viewed it?

You can see where I am going ...

 

[Marshall Brooks]

@jdraw and others:

I'm back with an odd issue ...

I decided that I would prefer to have real names in the Audit Table and have them stored that way initially.

Previously, I was using a function GetUserName to store the user ID. I think it worked with my bound forms also.

I added ELookup to the backend and created a new Function GetRealUserName() similar to Reply #78 to lookup the actual name and store it in tblAudit, or store the userid if it can't resolve the name.

It works fine when I manually change tblSource in the BE.
It works fine when I manually change tblSource in the linked table in the FE.
When I change the field via the bound form, I get an Error "3892 The function 'GetRealUserName' is not valid for expressions used in data macros."

But as I said, it works when I manually update the table, so I'm not sure that is really what the error is ???

Thoughts or suggestions?

It MIGHT be related to having Elookup() in both the FE and the BE, but I don't think so, since GetUserName is also in both the FE and BE and that was working properly before.

 

[jdraw]

In the test database you posted there was no ELookup.
Can you post the code?

Do you have an authoritative UserTable? Can you post the structure?
How about a revised test database to review?

 

[Josef P.]

GetRealUserName() is available in the FE?

 

[Marshall Brooks]

GetRealUserName() is available in the FE?

No, does it need to be?

 

[Marshall Brooks]

Public Function ELookup(Expr As String, Domain As String, Optional Criteria As Variant, _
    Optional OrderClause As Variant) As Variant
On Error GoTo Err_ELookup
' https://www.everythingaccess.com/tutorials.asp?ID=Extended/Replacement-ELookup%28%29
    'Purpose:   Faster and more flexible replacement for dLookup()
    'Arguments: Same as dLookup, with additional Order By option.
    'Return:    Value of the Expr if found, else Null.
    '           Delimited list for multi-value field.
    'Author:    Allen Browne. allenbrowne.com
    'Updated:   December 2006, to handle multi-value fields (Access 2007 and later.)
    'Examples:
    '           1. To find the last value, include DESC in the OrderClause, e.g.:
    '               ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
    '           2. To find the lowest non-null value of a field, use the Criteria, e.g.:
    '               ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
    'Note:      Requires a reference to the DAO library.
    Dim db As DAO.Database          'This database.
    Dim rs As DAO.Recordset         'To retrieve the value to find.
    Dim rsMVF As DAO.Recordset      'Child recordset to use for multi-value fields.
    Dim varResult As Variant        'Return value for function.
    Dim strSQL As String            'SQL statement.
    Dim strOut As String            'Output string to build up (multi-value field.)
    Dim lngLen As Long              'Length of string.
    Const strcSep = ","             'Separator between items in multi-value list.

    'Initialize to null.
    varResult = Null

    'Build the SQL string.
    strSQL = "SELECT TOP 1 " & Expr & " FROM " & Domain
    If Not IsMissing(Criteria) Then
        strSQL = strSQL & " WHERE " & Criteria
    End If
    If Not IsMissing(OrderClause) Then
        strSQL = strSQL & " ORDER BY " & OrderClause
    End If
    strSQL = strSQL & ";"

    'Lookup the value.
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
    If rs.RecordCount > 0 Then
        'Will be an object if multi-value field.
        If VarType(rs(0)) = vbObject Then
            Set rsMVF = rs(0).value
            Do While Not rsMVF.EOF
                If rs(0).Type = 101 Then        'dbAttachment
                    strOut = strOut & rsMVF!FileName & strcSep
                Else
                    strOut = strOut & rsMVF![value].value & strcSep
                End If
                rsMVF.MoveNext
            Loop
            'Remove trailing separator.
            lngLen = Len(strOut) - Len(strcSep)
            If lngLen > 0& Then
                varResult = left$(strOut, lngLen)
            End If
            Set rsMVF = Nothing
        Else
            'Not a multi-value field: just return the value.
            varResult = rs(0)
        End If
    End If
    rs.Close

    'Assign the return value.
    ELookup = varResult

Exit_ELookup:
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Err_ELookup:
    MsgBox Err.DESCRIPTION, vbExclamation, "ELookup Error " & Err.Number
    Resume Exit_ELookup
End Function

This was from Allen Browne's site, but I'm not sure it is still there.

 

[Josef P.]

The expression services run in the open Access instance ... the FE.
... Access BE is not an active DBMS BE.

 

[Marshall Brooks]

I didn't exactly follow all of that, but I created GetRealUserName() in the FE and all is good now.

Much appreciated.

 

[jdraw]

I split your sample database and created tblUserDemo in the BE.

tblUserDemo tblUserDemo

UserID	CompUserName	ActualUserName	OtherUserSpecificInfo
1​	kl	kanth Thpell	anndr998
4​	mc	My Kat	purr
3​	qr	Queen Resource
2​	jp	That’s Me	demo

I added this code to the top of your module1 and am using FOSUserName to get the user short name automatically and use that result to get ActualUserName for tblUserDemo.

#If VBA7 And Win64 Then
    'x64 Declarations
    Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
            "GetUserNameA" (ByVal lpBuffer As String, _
                            nSize As Long) As Long
#Else
    'x32 Declaration
    Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
            "GetUserNameA" (ByVal lpBuffer As String, _
                            nSize As Long) As Long
#End If

Function fOSUserName() As String
' Returns the network login name
    Dim lngLen As Long, lngX  As Long
    Dim strUserName           As String
   
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

' ----------------------------------------------------------------
' Procedure Name: GetRealUserName
' Purpose: Demo Function to get ActualUserName from tblDemoUser via CompUserName
' Procedure Kind: Function
' Procedure Access: Public
' Return Type: String
' Author: Jack
' Date: 10-Jan-24
' ----------------------------------------------------------------
Function GetRealUserName() As String
Dim UserShortName As String

'use FOSUserName to get actual computer user name

UserShortName = fOSUserName
'Lookup ActualUserName from tblDemoUser using UserShortName
GetRealUserName = DLookup("ActualUserName", "tblUserDemo", "CompUserName='" & UserShortName & "'")
End Function


Testing:

?fosusername
JP

?GetrealUserName
That’s Me

 

[Marshall Brooks]

@jdraw - basically what I did, but I think you will find if you are using a bound form in the front end and using GetRealUsername in the data macro, you have to add it to the FE also - at least I had to do so.

 

[jdraw]

Tables in BE, modules in FE. DataMacros are with the Tables(handled by Access).
I change the macro function to show the RealUserName.

Here is the updated tblAuditLog See entry #11

tblAuditLog tblAuditLog

PK	Orig_PK	REFERENCE	EVENT	OLD_VALUE	NEW_VALUE	USERNAME	MOD_DATE
3​	2​	kl-78922	Event 1 Complete	02-Mar-23	02-Jun-23	jp	08-Jan-24 6:26:29 PM​
4​	3​	jk-9533	Event 1 Complete	19-Sep-23	19-Oct-23	jp	08-Jan-24 6:28:13 PM​
5​	4​	BG-922	Event 1 Complete		03-Jan-24	jp	08-Jan-24 6:29:14 PM​
6​	5​	BG-9223	Event 1 Complete		02-Jan-24	jp	08-Jan-24 6:29:33 PM​
7​	2​	kl-78922	Event 1 Complete	02-Jun-23	02-Jun-24	jp	08-Jan-24 6:30:15 PM​
8​	5​	BG-9223	Event 1 Complete	02-Jan-24	02-Mar-24	jp	08-Jan-24 6:30:27 PM​
9​	6​	BHT-12	Event 1 Complete	30-Jan-24	01-Jul-24	jp	08-Jan-24 6:30:47 PM​
10​	1​	K-2345	Event 1 Complete	23-Dec-22	22-Dec-23	jp	08-Jan-24 6:32:12 PM​
11​	6​	BHT-12	Event 1 Complete	01-Jul-24	01-Aug-24	That’s Me	10-Jan-24 11:44:04 AM​

 

[Marshall Brooks]

Okay - I think I know what is going on now.

I added the modules to the BE. For testing, I was working with the data module in the BE and editing the table in the BE and got an error when it couldn't find the function.

Then in the FE, I got an error when it couldn't find the function.

I can probably comment out or remove the functions from the BE, but then I would get an error if I directly edited a table in the BE, but normally I wouldn't be doing that anyway.

Thank you again!!!

 

[Marshall Brooks]

@jdraw - Really dumb question time. I was looking at the .xml files you uploaded for me earlier and you had a comment in there:
NOTE:: You can copy a named macro via the immediate window using SaveAsText acTableDataMacro, "youtTableName", "Your directory and Filename and extension" My example: SaveAsText acTableDataMacro, "tblAuditLog", "C:\users\mellon\documents\macWriteAuditRec.txt"

So I open my test BE, press CTRL-G to open the immediate window, but the code in this with my directory info and press enter and the cursor moves to the next line and nothing happens ...

Disregard - it DID save the file, it just didn't show that it did anything initially!!!