How to create an automatically managed "last update" field with Microsoft Access

Question

Originally I thought to ask if there would be an easy way to provide an automatically managed last update field with MS Access.

After some googling I found following approach:

Private Sub Form_Dirty(Cancel As Integer)

   Me.Last_Update = Date()

End Sub

which seems to do the job. I thought to share it with others also (and if somebody has some good points that should be considered, feel free)

Best regards, Touko

Answer 1

That might be your best choice on an access database with an access back end- but if you've got a MS-SQL back end, put an update trigger on the table so that you can catch edits regardless of where they come from.

CREATE TRIGGER [Table_stampUpdates] ON [dbo].[Table]
 FOR Update 
AS 
BEGIN 
UPDATE Table
SET 
modified_by = right(system_user, len(system_user) - charindex('\', system_user)), modified_on = getdate() 
FROM Table inner join inserted on Table.PrimaryKey = Inserted.PrimaryKey
 END

Answer 2

Additionally, add a column Validation Rule (or CHECK constraint) to ensure the 'timestamp' column is updated when the table is being updated other than via your form. The SQL DLL (ANSI-92 Query Mode syntax) would look something like this:

CREATE TABLE MyTable 
(
   key_col INTEGER NOT NULL UNIQUE, 
   data_col INTEGER NOT NULL
)
;
ALTER TABLE MyTable ADD
   my_timestamp_col  DATETIME 
      DEFAULT NOW() 
   NOT NULL
;
ALTER TABLE MyTable ADD
   CONSTRAINT my_timestamp_col__must_be_current_timestamp
      CHECK (my_timestamp_col = NOW())
;

Another approach when using Jet 4.0 (pre-Access 2007 i.e. before user level security was removed from the engine) is to create a 'helper' Jet SQL PROCEDURE (Access term: stored Query object defined using an SQL 'Action' statement, as distinct from a SQL SELECT query) that automatically updates the 'timestamp' column then remove 'update' privileges from the table and grant them instead on the PROC e.g. SQL DDL/DCL something like:

CREATE PROCEDURE MyProc 
(
   arg_key INTEGER, 
   arg_new_data INTEGER
)
AS 
UPDATE MyTable
   SET data_col = arg_new_data, 
       my_timestamp_col = NOW()
 WHERE key_col = arg_key
;
REVOKE UPDATE ON MyTable FROM PUBLIC
;
GRANT UPDATE ON MyProc TO PUBLIC
;

The advantage here is all updates must go via the PROC and therefore is under the developer's control; the disadvantage is Access/Jet SQL is that your form will also have to use the PROC, which means a paradigm shift away from the standard 'data bound forms' approach for which Access is famous.

Answer 3

You could also put that same code into a BeforeUpdate.

The difference being that the OnDirty will tag the record when you first started to edit the record; while the BeforeUpdate will tag the record just before it gets committed to the database.

The latter may be preferable if you have a user who starts editing a record, goes to a meeting and then finishes editing it an hour later.