How to create a table containing the historical information about who and when modify

[lookingforK]

I am using MS Access 2007 to collect/store/extract data.

I would like to create a table that contains the information about who and when modify the database, i.e. the following table with the fields:

ID
Accessed by
Accessed time
Modified by
Modified time
Last saved by
Last saved time


How to achieve my goal? Should I use VBA?

Thank you in advance.

 

[thechazm]

I believe that's the only way to do that. You need to add something to the before update or after update event of each form or subform to add the necessary value's to the fields to accuratly keep track of who modified and when. One thing you also may want to add just as a side note is the [Creators] info just so you know who originally developed it but that's just my 2 cents.

 

[lookingforK]

Thank you thechazm,

I need to know who and when modify the database including all access objects, i.e. tables, queries, forms, reports, modules, and even VBA.:banghead:

Is this possible?

Thank you.

 

[Rx_]

Move your database back end to SQL Server - log events.
The VBA might be a tad bit tougher.
If it is really important and assuming you can't secure the vba (lock it down)
Then maybe move it to Citrix, require a log-in for the changes.
But, the built in parts don't really support that as far as I know.

On my applications, I add the Now() function to a field's default (created). The LastUpdated is a function that adds Now and the User name.
Besides the records, there are many controls that log an event such as the "add new" button click event - who, when, what primary key were they adding to...
Here is an example:

Function LogUsage_GIS(ByVal strFormName As String, _
    strCallingProc As String, Optional ControlName) As Boolean
10       On Error GoTo Err_LogUsage_GIS
          Dim rst As DAO.Recordset  ' The tLogUsage_GIS table
20            Set rst = CurrentDb.OpenRecordset("tLogUsage_GIS", 2, dbAppendOnly + dbSeeChanges)
30            rst.AddNew
40                rst![UseDate] = Now()
50                rst![strFormName] = strFormName
60                rst![CallingProc] = strCallingProc
70                rst![UserName] = Environ("username")
80                If Not IsMissing(ControlName) Then
90                    rst![ControlName] = Left(ControlName, 75)
100               End If
110           rst.Update
120           rst.Close
130           LogUsage_GIS = True
Exit_LogUsage_GIS:
140       Set rst = Nothing
150       Exit Function
Err_LogUsage_GIS:
          ' just resume next and the usage is not logged
  'MsgBox Err.Number & Err.Description
160       Err.Clear
170       Resume Exit_LogUsage_GIS
End Function