User and row level logging in MS Access 2010

Question

I recently developed an MS Access 2010 database and I want to add functionality that tracks the activity of each user. It occurs to me that I can create an Activity table and then add code to each form which adds a row to the Activity table whenever a user creates, views, edits, or deletes a record in the database. But how do I do the following:

1. Identify a specific user: Is there a User object so that calling something like User.getName() returns "JaneDoe" or "SallySmith" and I can thus store history for that specific user in the Activity table? What is the syntax for retrieving the current user's username?
2. Lock down tables permanently so that only specified forms can create, modify, or delete records in specific tables.
3. Make sure that the Activity table cannot be edited by anyone other than one specific administrator account.

So that readers can better understand what I mean with the above three questions, the structure of the Activity table might include the following fields:

id  (primary key)
userName  (the name of the user who made the operation)
tableName  (the name of the table that was changed)
operation  (the type of change, such as create, edit, delete)
rowNumber  (the autonumber primary key of the changed field)
date  (the date the change was made)

I might add more extensive logging, but setting things up at this level would enable us to make sure everything works before getting more sophisticated.

I am interested to obtain answers to my above three questions. Links to more information in your answers would be very helpful.

Is anything like this already built into MS Access 2010? Or do I have to roll my own?

Answer 1

For the Username problem, you can use the Windows API function GetUserNameW:

Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameW" (ByVal lpBuffer As Long, ByRef lpnSize As Long) As Long

Public Function GetWindowsUserName() As String
    Dim Bytes(0 To 511) As Byte
    Dim size As Long
    size = 256
    If GetUserName(VarPtr(Bytes(0)), size) = 0 Then
        'There was an Error calling the API function
    End If
    If size > 0 Then
        GetWindowsUserName = Mid(Bytes, 1, size)
    End If
End Function

If you want low level logging, I'd suggest that the best way of accomplishing it is to create a wrapper for the Recordset class that has all the same methods, but before calling the underlying Recordset members, performs whatever logging you want. Unfortunately, I tested and was unable to use `Implements DAO.Recordset". I received the error "Compile error: Bad interface for Implements: Interface is derived from another pure interface with non-restricted methods" - a new one for me :P