Jeff said:
Unlike SQL-Server and other more robust databases, Access/JET doesn't
provide a native, built-in way to determine this.
That said, it is fairly straight forward to add a field to your table(s) and
put the date/time when you add/edit. You'd want to do this through a form,
not by working directly in a table.
Following on from an earlier post, yes this *should* be enforced in the
database (I assume that's what you meant by 'working directly in a
table'). Jet has no way of automatically inserting the current
timestamp but it can and should check that is has been done e.g.
CREATE TABLE Test (
last_name VARCHAR(35) NOT NULL,
edited_date DATETIME
DEFAULT NOW() NOT NULL,
CHECK(edited_date = NOW())
);
INSERT INTO Test (last_name)
VALUES ('Boyce');
-- success, edited_date DEFAULT is applied
UPDATE Test
SET last_name = 'Codd';
-- fails, CHECK bites
UPDATE Test SET
last_name = 'Codd',
edited_date = NOW();
-- success, edited_date manually set to current timestamp
Because the value of NOW() may differ between server machine and local
machine, the NOW() keyword should be used. Better still, create a
PROCEDURE ('parameter Query') for updates that automatically sets
edited_date to NOW(), revoke permissions on the table and grant
permissions to the PROCEDURE i.e. the only way to update the table is
via the proc meaning the database programmer is in control.
From a design perspective, the OP should consider alternatively
maintaining a 'history' style table, with start_date and end_date
columns where a null end_date indicates the current status, etc.
