Security for program created in MS Access

[DanLo]

I recently developed an invoicing program using MS Access. A question came up about security:

If the owner is gone, how would he be able to track any changes to an invoice made by another employee. For instance, he is concerned that an invoice paid for by the Customer for $900 could later be changed to $700 and the additional $200 pocketed by an employee.

Is there some way to track keystrokes or changes made to a given record so that any questionable changes can be discovered? Any help or suggestions would be appreciated.

Thanks

 

[AnanthaP]

Access has a few OnChange type events and at least one each for Before record asnd OnChange in field and record values.

Using this you could write the old and new value to another change log table and dump / examine it later.

End

 

[DanLo]

Thanks! I appreciate your pointing me in the right direction. However, although I consider myself well versed in using MS Access, I know that my Visual Basic skills are sorely limited.

If I understand correctly, part of the Form properties (the OnChange event of the form) will allow me to create a log file that will list any changes to any given record. I will explore this line. But if you could give me any specifc help, especially with VB, I would greatly appreciate it.

Dan

 

[Terpsfan]

You can create a pseudo transaction log that will work for Access databases. I have created a procedure that should work for any form. I stick the function TransactionLog into a module or class module, then call it from the form's before update event. This will track all changes, sending the data to a log called tblLog.

In order to make this work you really need to create Access security so that you can reference the CurrentUser function that will obviously give you the logon of the current user.

If you need any further assistance, please feel free to ask.

Included in tblLog is the Primary Key value, old value of control changed, new value of control changed, user changing the record and a time stamp (default value of now()) that records the exact date/time the record was changed.

If you are using more than text box fields on the form you will need to reference that in the code when you loop through all the form's controls.

In the code below I am calling the transaction log function and plugging in two parameters, (me or current form, and "AutoID" which is the primary key of that form's table recordset)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call TransactionLog(Me, "AutoID")
End Sub
Public Function TransactionLog(ByVal frm As Form, ByVal strPrimaryKey As String) As Boolean
'Setup error handling
On Error GoTo TransactionLog_Err

'Declare DAO database objects
Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim ctl As Control
Dim strSQL As String

'Instantiate DAO database objects
Set db = CurrentDb
Set rst = Me.RecordsetClone


For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Then
If ctl.OldValue <> ctl.Value Then
'Create dynamic query to update transaction log with changes
strSQL = "INSERT INTO tblLog (AutoID, OldValue, NewValue,User) " & _
"VALUES (" & rst(strPrimaryKey) & ",'" & ctl.OldValue & "','" & ctl.Value & "',CurrentUser());"
'Use a transaction in case you need to rollback on error
DBEngine.BeginTrans
'Update the transaction log
db.Execute strSQL

'All is well, update the database
DBEngine.CommitTrans
End If
End If
Next ctl

TransactionLog_Exit:
'Dereference DAO Objects
Set rst = Nothing
Set db = Nothing
Exit Function

TransactionLog_Err:

DBEngine.Rollback
MsgBox Err.Number & vbCrLf & "An error has occurred, rollback changes"
Resume TransactionLog_Exit

End Function

 

[Terpsfan]

Actually I need to amend the above post. I didn't take into account the fact you should also store the fieldname being changed as well as tracking the recordsource of the form. Here is the amended code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call TransactionLog(Me, "AutoID", Me.RecordSource)
End Sub
Public Function TransactionLog(ByVal frm As Form, ByVal strPrimaryKey As String _
, ByVal strSource As String) As Boolean
'Setup error handling
On Error GoTo TransactionLog_Err

'Declare DAO database objects
Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim ctl As Control
Dim strSQL As String

'Instantiate DAO database objects
Set db = CurrentDb
Set rst = Me.RecordsetClone


For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Then
If ctl.OldValue <> ctl.Value Then
'Create dynamic query to update transaction log with changes
strSQL = "INSERT INTO tblLog (AutoID, OldValue, NewValue,User,RecordSource,FieldName) " & _
"VALUES (" & rst(strPrimaryKey) & ",'" & ctl.OldValue & "','" & ctl.Value & "',CurrentUser(),'" & strSource & "','" & ctl.Name & "');"
'Use a transaction in case you need to rollback on error
DBEngine.BeginTrans
'Update the transaction log
db.Execute strSQL

'All is well, update the database
DBEngine.CommitTrans
End If
End If
Next ctl

TransactionLog_Exit:
'Dereference DAO Objects
Set rst = Nothing
Set db = Nothing
Exit Function

TransactionLog_Err:

DBEngine.Rollback
MsgBox Err.Number & vbCrLf & "An error has occurred, rollback changes"
Resume TransactionLog_Exit

End Function