Chapter 25 Creating Audit Trails on Tables
Chapter 25 Creating Audit Trails on Tables
E
ven the simplest database can end up holding a lot of data, which is constantly
changing as users add new records and update and delete existing ones.The actions of the users may not have huge implications, but let’s suppose someone wants
to know who made a change and when and what they changed. In the past, I have written
applications producing reports for external regulatory bodies, where the need for audit trails
was of paramount importance.
A recent feature of Access on memo fields logs a change in terms of date and time but
does not tell you who did it or what they changed. To maintain the full story, you need to
customize it with VBA.
Who Is the User?
The first fact that must be established is who the user is and who has done the deed. You can
use an API call to find out who is actually using your application and altering data.
Create a new module by clicking Insert | Module on the VBE menu. Enter the following
declaration into the General area at the top of the module:
Public Declare Function GetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
289