How to update an access field w/the date everytime a change is made to its row?

Question

Hi and thanks for your help,

Note: I am using Access 2002

I am trying to update my "LastUpdatedDate" field with the NOW() date/time stamp if there is a change on its row.

Right now, I have followed the instructions that Doanair gave another guy trying to figure this out:

DATETIME to the table e.g. named LastUpdatedDate that indicates the last updated date/time of each row. Make it NOT NULL so that you will have to write an updated DATETIME value to that column for each INSERT or UPDATE. Also, set the column to have a default of DATE() for the current date stamp or NOW() for the current date/time stamp.

Unfortunately it still doesn't change the field if something is changed on the row. He mentioned that I should:

Then add a Validation Rule or CHECK constraint e.g. CHECK (LastUpdatedDate = NOW()) to ensure the column is actually updated on each UPDATE and INSERT.

But there isn't a check function in Access XP that I know of.

Any ideas guys/gals?

Thanks, Will

Answer 1

From what I understand, you will have to explicitly set the LastUpdated field's value to NOW(). It won't do it automatically on UPDATE.

You can set the Default Value of LastUpdated field to NOW() - that should take care of INSERTs.

Answer 2

I presume the Table is stored in an MDB file i.e. Native Access not in SQL Server?

If it is SQL Server you can create an Update trigger - no such option in Access Tables (or Jet Database Engine).

This means your are left with the option of setting the value everywhere the records are updated in form events on the client side.

for e.g.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.txtLastUpdated = Now()
End Sub

Where Me.txtLastUpdated is the name of a text box control.

Note a Check Constraint does not set a value - it just prevents the row from being updated if the Check fails.

Answer 3

Thanks for all of your help. I decided to just put a text field on my report with =now() and send them a pdf of that report. Adobe 1 Tech Guy 0

Will

Answer 4

The Access team hasn't yet got around to providing a means of creating a CHECK constraint using the Access GUI or DAO for that matter (here's hoping for Access2010!)

You must create the CHECK constraint using SQL DDL e.g.

ALTER TABLE MyTable ADD
   CONSTRAINT LastUpdatedDate__must_be_current_timestamp
      CHECK (LastUpdatedDate = NOW())

To run the above you must use ANSI-92 Query Mode.

Note you can also create a row-level Validation Rule using the table designer in the Access GUI:

LastUpdatedDate = NOW()

Just to clarify: a row-level Validation Rule is defined in the Access UI in the table's properties; in ADOX code it is confusingly called a Table Validation Rule (not sure what it is in DAO or ACEDAO). It will fire each time a row is UPDATEd.

As you can only define one row-level Validation Rule per table, IMO you are better off using a table-level CHECK constraint, which gets checked for every row in the table whenever one or more rows are UPDATEd. You can define multiple CHECK constraints per table and giving them meaningful names (LastUpdatedDate__must_be_current_timestamp, LastUpdatedDate__must_be_during_office_hours, etc) you will be able to provider user feed back with a greater degree of granularity than Validation Rules.

Answer 5

I don't see that anyone has given a very clear answer here.

Jet/ACE does not have triggers, so there's no way to define at table level a field that will be updated each time the record is updated.

Instead, you have to do this with the events of Access forms. The correct event of the form for this is the BeforeUpdate event, where you'd simply set LastUpdatedDate equal to Now(). You've already set the default value to Now(), which is fine so far as it goes, but as you said, that only populates the field when the record is inserted.

One issue you need to consider is that for new inserts, Now() as default value is going to give you the date/time of when you initiated the record insert. That is, you could start the new record, LastUpdatedDate would be set to the current date/time, and if you walked away for five hours and saved the record, the time would be 5 hours out of date. So, it may not useful to have the default value set to Now() -- you really can only be sure you're getting the right value by using the BeforeUpdate event of the form you use to edit it.