"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:43***********************@news-text.dial.pipex.com...
Hi
We have a set of complicated applications that access the same backend
database. One of the date fields if being blanked out from time to time by
an unknown process, which is causing problems in the organization as the
field is related to the payroll. Is there any way to place a check at
table level (kinda like sql triggers) to save the existing value of the
field when an app tries to modify it? This way at least we have a record
of what got changed and hopefully also when it got changed.
Thanks
Regards
No - there is nothing like triggers if the backend is an mdb. Obviously you
could design the table so that the date could not be null - ie no person or
process could 'blank it out' and you could could even ensure that the date
was between, say, Jan 1 2000 and Dec 31 2009.
If you really needed to make sure the field could not be altered, except
under very tightly controlled conditions, then you would have to use
user-level security and remove permissions from the table and write a piece
of code that logged on with a special account to do this. Then the only way
to do this would be to call a function like:
Public Function ChangeTheDate(dteNewDate As Date) As Boolean
The two problems with this are: firstly if other applications need to update
the table, then this may be too much work to change them all - and secondly
Access (Jet) security is breakble by anyone who wanted to break in. It
would be completely safe from any normal attempt to change the date (by any
person or process whatsoever), unless someone deliberately hacked the
security setup - and software to do this is widely available.
Perhaps you could migrate some or all the tables to SQL Server (or a free
version thereof). It would provide better security and triggers are built
in.