Record Date of Last Modification in Table

[Guest]

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

 

[Jamie Collins]

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.

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> ...

Authorized users may not always use your form to connect to
the database (e.g. Excel, VBA, etc) so implementing the 'timestamp'
mechanism in only one front end application is unsafe (especially if
this is for audit purposes).

Consider a simplified example. Say you wanted to maintain a 'date
inserted' column for a table:

CREATE TABLE MyTable (
key_col INTEGER NOT NULL UNIQUE,
data_col INTEGER NOT NULL,
effective_date DATETIME DEFAULT NOW() NOT NULL
);

Obviously you don't want the effective_date column to be written to
directly e.g. a user could accidentally (or otherwise) enter a far
future date; let's also say this metadata need not be exposed either,
so you wrap it in a view/query:

CREATE VIEW MyFakedTable
AS
SELECT key_col, data_col
FROM MyTable;

Revoke all permissions from the table and grant them to the view/query.


Users can now insert into the view/query and the default 'timestamp'
gets automatically applied:

INSERT INTO MyFakedTable (key_col, data_col) VALUES (1, 1);

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.

Jamie.

--

 

[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?

 

[Jamie Collins]

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?

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.

--

 

[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.

 

[Jamie Collins]

You're basically
tossing out bound forms.

If you <groan>must</groan> do bound forms then the minimum requirement
would be a CHECK constraint (or Validation Rule) such as:

CHECK (effective_date = NOW())

Jamie.

--

 

[Guest]

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