Autoupdate a Table Field

[polisci grad]

My database consists of three main tables: one for the data which contains
only an item key, a date, and a value for each record; another for the item
keys, which has descriptions and details of the items in the data table; and
a third to give details on data sets that consist of a series of items listed
in the item table. Here is an example of the relationships:

Data table records:
ITEMKEYS: DATADATE: DATAVALUE:
ALLCAR_US 2/1/2006 20980
ALLCAR_US 3/1/2006 20111

Item Table Record:
ITEMKEY: ITEMNAME: TABLEID:
ALLCAR_US All Carriers of Freight in US 4

Last Table Record:
TABLEID: TABLENAME: DATEMODIFIED:
4 Canada-US Trade by Mode 4/1/2006


The relationships are: 1TABLE.ITEMKEYS = 2TABLE.ITEMKEY, and 2TABLE.TABLEID
= 3TABLE.TABLEID

My question is: Is it possible to have the DATEMODIFIED field in the last
table automatically enter the date on which the data contained in the data
table was last modified/updated? As it is now, i have to manually update the
data in the the data table, then manually update the date that i last
modified the data in the last table.

Please do not bother commenting on the field names - they are only examples,
and i'm not concerned about changing this element of my database.

Thanks,

 

[Tim Ferguson]

My question is: Is it possible to have the DATEMODIFIED field in the
last table automatically enter the date on which the data contained in
the data table was last modified/updated? As it is now, i have to
manually update the data in the the data table, then manually update
the date that i last modified the data in the last table.

It depends on the database engine you are using. In a fullscale database
like SQL Server, MSDE etc you can use an update trigger to do this.
Unfortunately the Jet engine does not support this, so you can't do this at
the engine level.

The closest you can do is to intercept the form's (or forms') BeforeUpdate
or AfterUpdate event to set the value for you. Obviously this won't catch
updates being made by querydefs or vba or by people updating the table
datasheets directly. If the lastupdate field is a deal-breaker, then either
you need to upsize to a real RDBMS or tie down the Access application very
tightly.

Hope that helps


Tim F

 

[polisci grad]

I see - upgrading is not likely considering i barely know how to use Access.
I figure I'll just update them myself, or get rid of the field altogether.
Thanks.

 

[Tim Ferguson]

I see - upgrading is not likely considering i barely know how to use
Access.

Not much of an upgrade; if you can design a table in Access you can do it
MSDE almost as easily. Creating an ADP integrates the whole lot very
tightly.

I figure I'll just update them myself, or get rid of the field
altogether.

Not neccessary: it's quite easy to put the code into the form events. You
should be presenting your users with forms and hiding the table sheets
anyway.

All the best.

Tim F