Time Stamp Records

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

Hello everyone.

Does anyone know how I can time stamp a record each time I enter a new
record or edit an old one? I want a field to automatically update to the
date and time the record was last edited.

Any ideas on how to do that?

Dale
 
add a Date/Time field to your table. when adding/editing records via a form,
add code to the form's AfterUpdate event, as

Me!DateTimeFieldName = Now

if you're adding records via an Append query, in the DateTimeField column,
set the "Field:" to Now().

if you're updating records via an Update query, in the DateTimeField column,
set the "Update To:" to Now().

hth
 
Thanks. I will try that.

Dale


tina said:
add a Date/Time field to your table. when adding/editing records via a form,
add code to the form's AfterUpdate event, as

Me!DateTimeFieldName = Now

if you're adding records via an Append query, in the DateTimeField column,
set the "Field:" to Now().

if you're updating records via an Update query, in the DateTimeField column,
set the "Update To:" to Now().

hth
Click to expand...
 
Dear Dale and Tina:

You may want to consider the option of defaulting this column in the table
definition to Now(). This way, even rows entered directly into the table
will receive the time stamp.

A weakness is that the time is taken from each front end system. Make sure
the date and time on all systems are set accurately.
 
Relying on front end applications to maintain a business rule such as
'this date must always be kept current' is not a great idea. Relying on
e.g. Excel users to include it in all UPDATE commands may be foolhardy
<g>.

Access/Jet lacks triggers which would populate automatically but
there's no excuse for not including a constraint at the data engine
level (e.g. CHECK a.k.a. Validation rule) to ensure it is being done
e.g.

ALTER TABLE MyTable
ADD CONSTRAINT effective_date_must_be_current
CHECK(effective_date = NOW());
A weakness is that the time is taken from each front end system.
Click to expand...

Good point. I've wondered whether the above CHECK should have a degree
of tolerance in it but no one has picked up on this yet. Anyone?

To avoid the 'weakness', do things the proper way, ensuring the current
date/time from the database 'server ' machine is always used:
permissions should be revoked from tables, PROCEDUREs created to do the
INSERT/UPDATE using OWNERACCESS OPTION and supply the date in the
process, and permissions granted on the PROCs. In other words, make the
PROCs the only way to change the data and this the maintenance of the
'timestamp' is within the control of the (backend) database
machine/programmer rather than the client machine/front end programmer.

For example from this thread:
http://groups.google.com/group/microsoft.public.access.tablesdbdesign/msg/f25d1b1389678f52

Logon to database in 'ANSI 92' query mode as Admin, then

CREATE TABLE Test1
(key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL,
created_date DATETIME DEFAULT DATE() NOT NULL);

CREATE USER JohnV;

REVOKE ALL PRIVILEGES
ON TABLE Test1
FROM JohnV;

GRANT SELECT
ON TABLE Test1
TO JohnV;

CREATE PROCEDURE AddTest1
(:key_col INTEGER, :data_col INTEGER)
AS
INSERT INTO Test1 (key_col, data_col)
VALUES (:key_col, :data_col)
WITH OWNERACCESS OPTION;

GRANT UPDATE
ON OBJECT AddTest1
TO JohnV;

CREATE PROCEDURE UpdateTest1
(:key_col INTEGER, :data_col INTEGER)
AS
UPDATE Test1
SET data_col = :data_col
WHERE key_col = :key_col
WITH OWNERACCESS OPTION;

GRANT UPDATE
ON OBJECT UpdateTest1
TO JohnV;

Logon to database as JohnV, then

UPDATE Test1
SET created_date = #2002-02-02#
WHERE key_col = 1;
-- fails, cannot change created_date

INSERT INTO Test1 (key_col, data_col, created_date)
VALUES (2, 2, #2011-11-11#);
-- fails, cannot create row using created_date

EXECUTE AddTest1 2, 2;
-- success, created date is current

EXECUTE UpdateTest1 2, 999;
-- success, created date still is current

Note this code *is* Access/Jet SQL code (not to be mistaken for
MSDE/SQL Server T-SQL code, etc).
 
thanks, Tom, that is an alternate that Dale may want to use instead. i
rarely use it (i'd say never, but i never say never <g>), because the stamp
is applied as soon as you move to a new record - before data is even
inserted. in a "not high-production" environment, there may be a
considerable lag between moving to a new record in a form, and actually
creating the record. i prefer to stamp my records at the moment they're
saved to the table.
 
If writing for SQL Server, this would be the case. Most of those who post
here are using Jet, and there is NO SERVER. All operations to the database
are done through the client's shared access to the database on the server.
There is no way to implement what you suggest in Jet. The date/time on the
clients will determine the date/time of such a timestamp, and they can be in
error. That is because there is no "Server Application" running that could
possibly do this job.

I can certainly default or constrain an MSDE/SQL Server database as you
suggest, and would certainly do so.
 
Tom said:
All operations to the database
are done through the client's shared access to the database on the server.
Click to expand...

Are you saying when the resides .mdb (with containing proc) resides on
a remote machine, the proc is executes on the local machine anyway?
There is no way to implement what you suggest in Jet. The date/time on the
clients will determine the date/time of such a timestamp, and they can be in
error.
Click to expand...

Well, you could still implement what I suggest in Jet and it would
ensure the local machine's current date/time was always used i.e.
preventing a user/application from INSERTing using a date/time other
than NOW() and blocking an UPDATE which didn't also change the value to
NOW(). If you would do this in SQL Server, will you now do it in Jet?
 
Responses inline.
--
Tom Ellison

server.

Are you saying when the resides .mdb (with containing proc) resides on
a remote machine, the proc is executes on the local machine anyway?
Click to expand...

In every case. The "server" is not even required to have Access or any part
of it installed. It is only a file sharing site.
Well, you could still implement what I suggest in Jet and it would
ensure the local machine's current date/time was always used i.e.
preventing a user/application from INSERTing using a date/time other
than NOW() and blocking an UPDATE which didn't also change the value to
NOW(). If you would do this in SQL Server, will you now do it in Jet?
Click to expand...

Because there is only a file sharing server, and all processing is done on
the local "client" systems, and since each "client" has its own date/time,
there is therefore no way this is guaranteed to be synchronized. That is
not to say a programmer could not synchronize this using some interprocess
communication between a "time server" on the network and all the "clients",
but this is not a native capacity of Access when using Jet. Using MSDE, the
action of defaulting a time stamp column on the server would avoid this
difficulty, because the server is where the MSDE is being run.
 
Tom said:
Because there is only a file sharing server, and all processing is done on
the local "client" systems, and since each "client" has its own date/time,
there is therefore no way this is guaranteed to be synchronized.
Click to expand...

I've moved on from synchronization of date/time between machines. I now
mean, ensuring (1) the date/time column is maintained and (2) ensuring
the only possible value is the local machine's current date/time. You
agree that it is a good idea for SQL Server for synchronization
reasons, do you not agree it is still good for Access/Jet for data
integrity reasons?
 
Back
Top