How to create an automatically managed "latest update" in Microsoft Access

Question

I originally thought to ask if there is an easy way to provide an automatically managed last update field using MS Access.

After some searches, I found the following approach:

Private Sub Form_Dirty(Cancel As Integer)

   Me.Last_Update = Date()

End Sub

Which seems to do the job. I thought I would share it with others (and if someone has some good points to consider, feel free to share them)

Answer 1

You can also put the same code in BeforeUpdate.

, OnDirty , ; BeforeUpdate , .

, , , , .

Answer 2

, ( CHECK), , "timestamp" , , . SQL DLL ( ANSI-92) :

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())
;

Jet 4.0 (pre-Access 2007, .. , ) - "" Jet SQL PROCEDURE ( : , SQL- ', SQL SELECT), "timestamp", "" PROC, SQL DDL/DCL - :

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
;

, PROC , , ; Access/Jet SQL , PROC, , , .

Answer 3

This might be your best choice in a back-end access database, but if you have MS-SQL back end, put an update trigger in the table so you can catch changes no matter where they are 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