Audit Update of a Field

[Marshall Brooks]

Probably a simple question, but I wasn't sure what search terms to use.

Currently, our database has date fields for (example) "Subprocess A Completed". Currently, any user can update these fields. My supervisor requested two changes:

• Restrict the field update to only certain users - probably by user name. I know how to obtain the user name, and I can probably figure out how to implement this, not sure if I would use the beforeUpdate or AfterUpdate event - I'm thinking BeforeUpdate.
• Have some way to audit the information - i.e. if the Field has a date of 4-Dec-2023, be able to say "Marshall Brooks updated that field at 12:53 P.M. on 4-Dec-2023" (probably okay to show usernames instead of actual names and probably don't need exact times). I think this is possibly, but I don't know how to accomplish it.

Thanks in advance!!!

 

[CJ_London]

Look at using data macros

 

[Marshall Brooks]

https://support.microsoft.com/en-au/office/create-a-data-macro-b1b94bca-4f17-47ad-a66d-f296ef834200 - I see how to set up the data macro, but not exactly how to log information using it.

 

[GPGeorge]

You write records to an audit table. Data macros fire when the event to which you attach them occur, e.g. an update to an existing record. You can use the data macro to write into that audit table the old value and the new value and the name of the field updated, along with the date/time and user who made the change. Explore the macro actions available--they are far more limited that than VBA. It may take some study, but the learning curve isn't overwhelming.

 

[Pat Hartman]

Restrict the field update to only certain users - probably by user name. I know how to obtain the user name, and I can probably figure out how to implement this, not sure if I would use the beforeUpdate or AfterUpdate event - I'm thinking BeforeUpdate.

Do not hard-code the list of people who can do the updates. Create a table and a form to manage the table. This form needs different authorization. You don't want to have to change code to change the users. The BeforeUpdate event is the correct event because you can cancel it if the user is not authorized. Validation ALWAYS needs to go into an event that provides a Cancel argument. Best is the form's BeforeUpdate event. Although, in this case, since it is only the single field that you are validating, you could use the control's BeforeUpdate event.

There are a few options for doing logging. Look in the "similar threads" below for ideas.

 

[Marshall Brooks]

@PatHartman - Thanks, I was thinking I needed a table with a field for who could perform the updates. You suggested this when I implemented the switchboard form. I should have listened to you then. It's still not too late.

Similar threads for me doesn't seem very useful:

But now that I know what to look for, I can probably search for audit table, etc.

 

[ebs17]

@CJ_London @GPGeorge
A DataMacro works at table level, i.e. in the backend.
How can this DataMacro determine without any doubt from which front end a record was accessed by which logged in user?

 

[Marshall Brooks]

Which front end is not significant. Which logged in user is significant.

 

[Pat Hartman]

Sorry, I thought this one would show up.

Allen Browne - Creating an Audit Log

How to create an audit log to record the history of changes to records in a Microsoft Access database - edits, inserts, and deletions.

allenbrowne.com

I also wouldn't use a data macro.

 

[Marshall Brooks]

I also wouldn't use a data macro.

But from the Allen Browne page you posted ...
(Note: Access 2010 contains Data Access Macros (effectively triggers), — a better way to create an audit trail if you use the new database format.)

I don't have a preference and don't know the advantages or pitfalls of either one, but I don't want to make the changes and find out I chose the wrong approach.

 

[jdraw]

There is a sample database with data macros here and a discussion in the thread.
Data macros are specific to ms access ---not transferable to other dbms.

 

[Pat Hartman]

I like the concept of Data Macros but the Macro interface is simply too awful to work with.

 

[CJ_London]

A DataMacro works at table level, i.e. in the backend.
How can this DataMacro determine without any doubt from which front end a record was accessed by which logged in user?

It can’t but that does not appear to be a concern for the OP

 

[GPGeorge]

@CJ_London @GPGeorge
A DataMacro works at table level, i.e. in the backend.
How can this DataMacro determine without any doubt from which front end a record was accessed by which logged in user?

Like Marshall said. You record the logged in user who made the change. If an application doesn't capture User information, it can't do that, of course. But in a multi-user environment where audit logging is required, one would normally expect to have that User information available. You'd need to call a function to retrieve it in the Data Macro. Also, the assumption is that the user has logged into Windows on whichever computer they are using.

There are a number of ways to capture the logged in user on a given computer, of course.

Here's one I've had in my library for many years, for example. It was update for 64 bit at some point, and might be in line for another refresh, but it still returns the correct UserName.


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

Public Function OSUserName() As String
Dim lngLen As Long
Dim lngX As Long
Dim strUserName As String



4810 strCtl = Screen.ActiveControl.Name
' Returns the network login name
4820 strUserName = String$(254, 0)
4830 lngLen = 255
4840 lngX = apiGetUserName(strUserName, lngLen)
4850 If lngX <> 0 Then
4860 OSUserName = Left$(strUserName, lngLen - 1)
4870 Else
4880 OSUserName = vbNullString
4890 End If

End Function

 

[Josef P.]

A DataMacro works at table level, i.e. in the backend.
How can this DataMacro determine without any doubt from which front end a record was accessed by which logged in user?

However, the DBEngine runs on the client. And if you edit the linked table in the frontend, you can even use frontend functions in the data macro.
At least, if that's what you want to do

 

[ebs17]

if you edit the linked table in the frontend, you can even use frontend functions in the data macro

Great cinema, it works, of course.
In addition to the bound form, update queries and append queries can also be used, and recordsets can certainly be used too. Currentdb.Name, i.e. the name of the front end, could also be passed in this way.

What documentation did the “Evaluate/Eval” instruction come from? The expression builder didn't offer that.
But this was a brilliantly simple solution.

I noticed an additional effect: You can no longer edit the table directly in the backend by hand, neither make additions nor edits (still deletes). You get an error with the message "missing", which is very empty of content for an in-depth evaluation.

This effect can be very annoying, but it could also be used specifically if you want to additionally protect your file backend from unwanted changes by third parties.

 

[Josef P.]

What documentation did the “Evaluate/Eval” instruction come from?

I just gave it a try, since I now lack experience with access backends.
In my daily practice, I only use active DBMS as backend.

I noticed an additional effect: You can no longer edit the table directly in the backend by hand, ...

You would only have to add the required functions to the Access backend.

 

[The_Doc_Man]

OK, I'm a little confused here, maybe a bit denser than normal - but then I haven't had my morning jolt of caffeine yet.

Are you actually allowing people to see a raw table? Talking about data macros is all well and good, but if you want to really control who does what to whom on Saturday, you have to have a form standing between the end user and the tables. And if you ARE talking Access data macros, you are talking about an Access Back End, since non-Access back ends wouldn't call this a data macro. If it is an Access back end, you are also not talking about web interfaces.

To protect data and perform audits, put up a form. You then put the logic in the form. From there you can do everything you said that the boss requested of you.

From the original description, it SEEMS that users have a direct path to the unprotected tables. Please clarify the issue regarding what the user actually sees.

 

[GPGeorge]

OK, I'm a little confused here, maybe a bit denser than normal - but then I haven't had my morning jolt of caffeine yet.

Are you actually allowing people to see a raw table? Talking about data macros is all well and good, but if you want to really control who does what to whom on Saturday, you have to have a form standing between the end user and the tables. And if you ARE talking Access data macros, you are talking about an Access Back End, since non-Access back ends wouldn't call this a data macro. If it is an Access back end, you are also not talking about web interfaces.

To protect data and perform audits, put up a form. You then put the logic in the form. From there you can do everything you said that the boss requested of you.

From the original description, it SEEMS that users have a direct path to the unprotected tables. Please clarify the issue regarding what the user actually sees.

Good point. In my naivety, I just assumed that there is an Access FE and that users are not actually editing raw tables.

 

[ebs17]

Are you actually allowing people to see a raw table?

No, definitely not for users. But the developer might be inclined to do something like that. In addition, an access file can be found and copied. An Access backend is not completely isolated everywhere.

To protect data and perform audits, put up a form.

Working only via bound forms is a bit one-sided. Table entries can also be made via action queries, record sets and standard imports. Doesn't something like that happen to you?

I just enjoyed my cappucchino, but I'm about to get a new one.