Tracking Changes to Data

[presslc]

Hi!

One my App Owner's requirements is to track changes made to data. I have a 'Before Update' event on my form that creates an audit trail of changes that happen to the data through the form, but I don't know how to track changes to the data if it's done directly in the table.

I don't mind preventing the users from accessing the table, but I'm not sure how to do that either. I have user-level security on the database, but I can't find a way to prevent the user from opening the table (the Open/Run checkbox is disabled).

Any suggestions to either a) auditing changes made to the data not through the form, or b) restricting access to the table, but not the form?

Any advice is appreciated.
Thanks ahead of time!

 

[PassingBy]

Pressic
Sorry can't help you with your problem but I am interested in your data change tracking methods, can you post a brief description of how you 'record' record changes in a trail.

Thanks

PassingBy

 

[presslc]

Hi PassingBy!

All this code does is write either:
'Created by User on Date' or
'Changed by User on Date'. User = the network username.

If this results is satisfactory to you, here's what you do:

Create a new module and paste this code in it (or add to an existing one):

Note: My form is called TabbedForm and my variable name is investorForm. Change the code to be consistent with your form name.
****************************
Function AuditTrail()
On Error Resume Next
Dim investorForm As Form_TabbedForm
Dim x As Integer
Dim c As control

Set investorForm = Screen.ActiveForm
Set c = investorForm.Controls(0)

'If record is new, record that it was created.
If investorForm.NewRecord = True Then
investorForm!Audit = investorForm!Audit & Chr(13) & Chr(10) & "Created on " & Date & " by " & NetworkUser() & ";"
GoTo getouttahere
End If

'First check to see if the record has been udpated
'Get 1st control
x = 0
c = investorForm.Controls(0)
For Each c In investorForm.Controls
Select Case c.ControlType
'Only compare the control if it's a data entry type
Case acTextBox, acCheckBox, acComboBox, acListBox, acOptionGroup
'Make sure you're not checking audit field
Dim myname As String
myname = c.Name
If c.Name <> &quot;Audit&quot; Then
If c.OldValue <> c.Value Then

x = 1
End If
End If
End Select
Next c

'Udpate the doc if change (x = 1)
If x = 1 Then
'Set date and current user if form has been updated.
investorForm!Audit = investorForm!Audit & Chr(13) & Chr(10) & &quot;Changes made on &quot; & Date & &quot; by &quot; & NetworkUser() & &quot;;&quot;
End If
getouttahere:
End Function
*********************

Then, type =AuditTrail() in the Before Update event of your form properties.

Let me know if you run into problems. Good Luck!
Che

 

[presslc]

PassingBy -

I forgot a part.

Paste this code into the same module as you do the code above. It will give you the network username:

Function NetworkUser() As String
On Error Resume Next
'Hacked up code from ACC: How to Retrieve Workgroup Information Under Win32
'PSS ID Number: Q148835
Dim cbusername As Long, UserName As String
Dim ret As Long
UserName = &quot;&quot;

' Windows 95 or NT - call WNetGetUser to get the name of the user.
UserName = Space(256)
cbusername = Len(UserName)
ret = WNetGetUser(ByVal 0&, UserName, cbusername)
If ret = 0 Then
' Success - strip off the null.
UserName = Left(UserName, InStr(UserName, Chr(0)) - 1)
Else
UserName = &quot;&quot;
End If
NetworkUser = UserName
End Function

Sorry about the add on.

 

[PassingBy]

Pressic
Thanks for posting your method, sorry about delay in reply - serious network errors this morning!

This is sort of along the lines, I already extensively use the Environ (&quot;username&quot function rather that the workgroup to record user activity within table fields.

What I am really looking for is a transactional based audit facility to record all details before any edit takes place to a record so as to build a 'life history' of the record.

So far, I have worked on the principle of:-

1) Copy existing record prior to editing
2) Create new record based on existing record values
3) Allow edits to new record

The problem with this method is it doesn't seem to flow very well and I have had to code various workarounds to ensure the audit is complete.

To put you in the picture, I am working with insuarnce policies and quote systems and here are some examples of where maintaining a thorough audit trail becomes a problem:-

A quote may be logged under a unique quote ref (tableID) and then change to an allocated policy number (new uniqueID)

Premium transactions need to be tracked for many possible changes such as revisions/reconciliation/contra entries etc

Risk values etc need to be tracked for changes

I'm not sure that Access is the best db to be using for transactional based system but I have no other choice, this is why I was keen to hear of your methods.

PasssingBy

 

[PassingBy]

Pressic
I maybe able to throw some light on your original problem.

Because our users are all thin client (Citrix ICA client)I have not utilised the workgroup security, there are many arguments in these forums about how secure dbs are without workgroup admin but this method satisfies our needs.

Basically I use a master user table in my db to tie down access to parts of the db using the Environ (&quot;username&quot function. This control allows you to prevent a thin client from opening the db unless their Citrix login is validated by the db table.

By simple Yes/No attributes in the ID table, I am then able to define who has access to which data, the runtime only session prevents the user from getting to the underlying tables.

Hope this is some help

Passingby

 

[presslc]

PassingBy -
Thanks for the tip! That could be my solution.

Unfortunately, I don't have a good solution for you. Here's a page that discusses tracking changes in more detail than what I'm doing.

http://www.vb123.com/toolshed/00_access/auditrecords.htm

This page may help with your need to track changes to data such as revisions/reconciliation/contra entries etc, but I don't know what to suggest if a whole new record is (new id) is created from an existing one, unless you create an action to do so that would write to the log before it creates. Although that does you no good if the user changes his mind and doesn't save the record. You've got a doozy buddy. Sorry I can't be of more help!

Good Luck )

 

[PassingBy]

Pressic
Thanks for the link details, it looks just what I'm after, I will have a good read and digest.

Bye for now

PassingBy ... again

 

[assets]

pressic,
I have just read this thread. Can you please explain the two forms. If I have a form called assets ( the table is also called assets)I want to record changes made to this form. How do I use the second form?
Thank for any help