Audit Update of a Field (1 Viewer)

[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.

 

[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.

 

[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.

 

[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.

 

[GPGeorge]

The ad

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.


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.

The advantage of Data Macros in Access tables is that they fire regardless of how data is changed: via a form, an action query, record sets or standard imports, or even the actions of the developer!

With regard to things like finding and copying an Access back end, I have to wonder if that is not subject to a whole different topic of discussion. The data changes in a copied version of the accdb would not be reflected in the original, production copy, but could be recorded via the data macro in the copied accdb. Perhaps that's a good thing because, if the owner ever did recover that copied accdb, the audit table in it could document who did the deed.

 

[ebs17]

The advantage of Data Macros in Access tables is that they fire regardless of how data is changed: via a form, an action query, record sets or standard imports, or even the actions of the developer!

There was no question about that. Everything that has to do directly with records comes together at table level.

The question was how something determined by VBA could get to this table level.

 

[GPGeorge]

Ah, yes. Of course. Again, I was thinking of the context of a working production application with the audit function. Thanks for the reality check.