Audit Users/Login databases/tracking

Question posted by: MUHAMAMD SALIM SHAHZAD (Guest) on November 13th, 2005 01:15 AM

dear respected gurus,

I would like to share ideas...as i learned from you and wish to tell
that i had developed the system where i can audit each and every users
and their actions(like add/edit/save/cancel/unco)

=======================start of
modules=======================================
Option Compare Database
Option Explicit

Public m_UserID As String
Public m_DbName As String

Public sqlLogIn As String
Public sqlAudit As String

Public sqlAllow As String
Public rs As DAO.Recordset

Public Function UserLogin(varUserID As String, varLoginOut As String,
varFrmName As String)

m_DbName = "AEUW"

sqlLogIn = "INSERT INTO tblLogIn " _
& " ( UserID,LogInOut,dbName,frmName,dtInOut )VALUES" _
& " ('" & m_UserID & "','" & varLoginOut & "','" & m_DbName & "','" &
varFrmName & "',now());"

DoCmd.SetWarnings False
DoCmd.RunSQL sqlLogIn

End Function

Public Function ClassAudit(varClass As String, varAction As String,
varType As String, varTypeID As String)

sqlAudit = "INSERT INTO tblAudit " _
& " ( UserID,Class,Action,Type,TypeID,DateIn )VALUES" _
& " ('" & m_UserID & "','" & varClass & "','" & varAction & "','" &
varType & "','" & varTypeID & "',now());"
DoCmd.SetWarnings False

DoCmd.RunSQL sqlAudit

End Function

Public Function UserAllowed(ByVal varAllow As String) As Boolean

sqlAllow = "SELECT tblDB.db_aeuw FROM tbldb WHERE tblDB.userid ='"
& m_UserID & "';"
Set rs = CurrentDb.OpenRecordset(sqlAllow, dbOpenSnapshot)

UserAllowed = rs.Fields("db_aeuw")
rs.Close
Set rs = Nothing
End Function
=======================end of
modules=======================================

then in the login forms i did
+++++++++++++++++++++login forms========================
Option Compare Database
Option Explicit
Dim i As Integer
Dim strUserID As String
Dim strPWEntered As String
Dim strPWActual As String



Private Sub btnExit_Click()
[txtUserID] = ""
[txtPassword] = ""
DoCmd.Quit
End Sub
Private Sub cmdLogIn_Click()
' verify a user id and password have been entered. If not, display an
error message and end the sub
If IsNull([txtUserID]) Or [txtUserID] = "" Then
MsgBox "You forgot to enter your UserID. Please try again.", ,
"User Name Message"
txtUserID.SetFocus
Exit Sub
End If

If IsNull([txtPassword]) Or [txtPassword] = "" Then
MsgBox "You forgot to enter your password. Please try again.", ,
"Password Message"
txtPassword.SetFocus
Exit Sub
End If

' otherwise...
' declare variables

strUserID = [txtUserID]
strPWEntered = [txtPassword]

' verify the UserID is valid (in the table). If not, display and
error message and end the sub.
' This validation can also be done in the AfterUpdate event of the
txtUserID control
i = DCount("[UserID]", "tblUsers", "[UserID]='" & strUserID & "'")

If i = 0 Then
MsgBox "You have entered an invalid UserID. Please try again.", ,
"Invalid UserID Message!"
[txtUserID] = ""
[txtPassword] = ""
txtUserID.SetFocus
Exit Sub
End If

' look up actual password
strPWActual = DLookup("[PAssword]", "tblUsers", "[UserID]='" &
strUserID & "'")

' compare passwords. If they match, log in and set the global UserID
variable. If they don't, display
' an error message and end the sub
If strPWEntered = strPWActual Then
'MsgBox "You are logged into the database"
'[txtUserID] = ""
'[txtPassword] = ""

m_UserID = strUserID

'DoCmd.Close
'DoCmd.OpenForm "mainswitch2000"
' any other login code here

If UserAllowed(m_UserID) Then
Call UserLogin(m_UserID, "LogIn", "Master")
DoCmd.Close
DoCmd.OpenForm "mainswitch2000"
Else
MsgBox "Mr. " & m_UserID & " not allowed to open Arab_Eastern
Underwriting System"
[txtUserID] = ""
[txtPassword] = ""
txtUserID.SetFocus
End If

Exit Sub
Else
MsgBox "You have entered an invalid password. Please try again.",
, "Invalid Password Message"
[txtUserID] = ""
[txtPassword] = ""
txtUserID.SetFocus
Exit Sub
End If

End Sub
Private Sub Form_Load()
[txtUserID] = ""
[txtPassword] = ""
End Sub
+++++++++++++++++++++++++++++++++++++++++++++++++

one every forms that allow to read only
so i call the above functions and pass the values everytime when user
hit any actions buttons


above codes runs in module, i created one audit.mdb, where only tables
exsists...make userid(pk)/password, then child tables of details like
which db to logon, when login and when to logout, who edit/save which
record

so this is just to sharing ideas....any ideas more to improved like
one user can open in ready only and other can edit etc...

or any one who has developed like users/staff auditing system in
access

yes above codes running in 4-5 various databases

onething how can i hide userid tables from ppls, how can i disable
shift key to open in background..

thansk and kind regards....thansk a lots again to help me developing
this system

shahzad

Quote | Subscribe