Audit Users/Login databases/tracking

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

re: Audit Users/Login databases/tracking

msssd786@yahoo.ca (MUHAMAMD SALIM SHAHZAD) wrote in
news:5a7850d5.0406210244.1216adee@posting.google.c om:
[color=blue]
> 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...
> shahzad[/color]

Have you considered keeping your login form open but hidden, with a public
property set to the login recordset? In such a situation, one does not have
to open the recordset over and over again as various forms are opened and
buttons clicked, but simply to refer to that recordset, with something like
frmLogin.rLogin, and its fields, and to check values there.

--
Lyle
(for e-mail refer to http://ffdba.com/)

re: Audit Users/Login databases/tracking

Shahzad,
There are two ways to disable the shift key, Permanent and temporary.
I wont bother telling you the temporary. However, for the permanent,
make a "Secured" mdb file and copy the following code in a module
(from Microsoft's site)
Also, make two Macros called enable and disable. For disable, use
faq_DisableShiftKeyBypass(True)
For enable use
faq_DisableShiftKeyBypass(False)

NOTE: do not run any of these macros in the mdb file.
Now create a .mde file, and run faq_DisableShiftKeyBypass(True) from
the mde file. (You have to be an admin to do this)
Off course you have to disable a lot of other stuff to lock yourself
out
Hope this helps

Thanks
Farooq




Function faq_DisableShiftKeyBypass(fAllow As Boolean) As Boolean

On Error GoTo errDisableShift

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim prop As DAO.Property
Const conPropNotFound = 3270

Set ws = DBEngine.Workspaces(0)
Set db = CurrentDb

db.Properties("AllowByPassKey") = Not fAllow
faq_DisableShiftKeyBypass = fAllow
exitDisableShift:
MsgBox "Successfull"
Exit Function

errDisableShift:
'The AllowBypassKey property is a user-defined
' property of the database that must be created
' before it can be set. This error code will execute
' the first time this function is run in a database.

If Err = conPropNotFound Then
' You must set the fourth DDL parameter to True
' to ensure that only administrators
' can modify it later. If it was created wrongly, then
' delete it and re-create it correctly.
Set prop = db.CreateProperty("AllowByPassKey", _
dbBoolean, False, True)
db.Properties.Append prop
Resume
Else
MsgBox "Function DisableShiftKeyBypass did not complete
successfully."
faq_DisableShiftKeyBypass = False
GoTo exitDisableShift
End If
End Function