Tarpping changes to a field

J

John

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
 
D

dkintheuk

You could always put an afterupdate event in both the front end and the
back end databases...

The event would write the current username to a table (see
www.mvps.org/access) for some straightforward code to find the username
then write it to a table with a recordset add function with current
date and time.

I would suspect it is more likely that a rouge user is doing something
(possibly inadverantly) to amend the date field.

If it is an automatic process you need to debug your code to make sure
you have explicity added a line that is blanking the date when that
code is run...
 
D

dkintheuk

Actually, here is the code from MVPS to help you out.

'******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If ( lngX > 0 ) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function
'******************** Code End **************************


then create an afterupdate subroutine that does something like...

Sub writeusername()

dim username as string, db as database, rs as recordset

username = fOSUserName()

set db = currentdb()
set rs = nothing 'just in case
set rs = db.OpenRecordset("AuditTable")

rs.AddNew
rs![username] = username
rs![datetime] = date()+time()
rs.update

set rs = nothing
set db = nothing

end sub

Make sure you create a table in the back end (and link it to the front
end) that is called AuditTable and give it two fileds - 'username' and
'datetime'.

Enjoy...
 
B

Brian Wilson

John said:
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
Click to expand...

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