Record Date of Last Modification in Table

[JD McLeod]

Is it possible to add a field to a database for date last modified, kind of
like a time stamp? I could then run a report and see how many records were
either added or modified in a day?

What I want to do is monitor the activity to see if users are enter records
into our database each day or waiting and adding them all at one time. Is
there a better way to do this?

 

[Rick Brandt]

Is it possible to add a field to a database for date last modified,
kind of like a time stamp? I could then run a report and see how
many records were either added or modified in a day?

What I want to do is monitor the activity to see if users are enter
records into our database each day or waiting and adding them all at
one time. Is there a better way to do this?

Only if you ALWAYS update using a form and have code in the form apply the date.
You cannot define this at the table level in a Jet (mdb) table. In a form you
would use the BeforeUpdate event...

Me!LastModified = Now()

(or if you do not want the time included)...

Me!LastModified = Date()

 

[Rick Brandt]

I'm glad you agree such metadata requirements should be implemented at
the engine level if possible. And you will no doubt be glad when I
tell you how that can be achieved <g> ... [snip]
Of course, you are more likely to want a 'last modified' date i.e. the
effective_date column is additionally maintained for updates. For
Access, a stored proc/parameterized query is required e.g.

CREATE PROCEDURE SetMyTableData (
arg_key_col INTEGER,
arg_data_col INTEGER
) AS
UPDATE MyTable
SET data_col = arg_data_col,
effective_date = NOW()
WHERE key_col = arg_key_col;

Obviously you would now revoke update permissions from the view/query.

You say this is being "implemented at the engine level". Exactly how is the
engine going to call this procedure when a user does an update?

 

[Rick Brandt]

When a user 'does an update' where exactly? UPDATE permissions have
been revoked from the table/VIEW.

If the user wants to add data they can INSERT into the VIEW (or
execute the appropriate procedure) and the default is applied. If
they want to modify existing data, they call the appropriate
procedure.

Jamie.

Okay well you have different interpretations of things then I do. Forcing
all updates to be done via procedures (update queries in Access) is NOT what
I consider "implementing things at the engine level" and is something I
would never recommend when using Access as the client. You're basically
tossing out bound forms.

 

[Kieron White]

I have a series of textboxes within a form and I am looking for some sample
code which will allow me to record the time and date , in effect a time
stamp, of
when other textboxes have been modified in an additional textbox.

For example : Plan Date Text Box, Actual Date Text Box, Comments Text Box
I am planning to record the time and date when any of these fields are
modified.

Many Thanks

Kieron White