Multi User / Level Security

[bodvoc]

Hi All

Has anyone any good tips or techqinues to do security on an Access 97 db.

I have 14 users, most need to add data and / or modify data in various forms and sub-forms, only a couple of them need just to view exisiting data.

Don't want to use MS Access's built-in stuff and get bogged down with workgroups...

Also need to track who has done what, i.e added data or modified data. Viewing data doesn't need to be logged in any way.

Thanks to all that reply

Bodvoc

 

[WayneRyan]

Bodvoc,

If you implement your own security, you can make use of popup,
modal forms. You can control what forms are run by command
buttons, checking the users rights before launching them. With
the forms being modal, the user is "trapped" in them.

Also, you can launch a form as read-only or read-write, so you
don't have to develop different sets of forms.

You can search on this forum, and there are hundreds of posts
on "Security" and "Audit Trail".

There are also many tips on customizing menu bars and hiding
the database window on startup.

Wayne

 

[hollering]

Write your own security Module

One way to do security on an Access 97 database is to start with a modal login form and write a module that controls priveleges. The one I did simply authenticates a user to allow access into the database and then opens forms in read-only mode if the user is not an admin. When using this approach you should also limit the users ability to switch from form view, and also may want to disable Access's special keys.

Here's what my getAuth module looks like

--------------------------------------------
Option Compare Database
Option Explicit

Private confirmUser As Boolean
Private confirmAdmin As Boolean
Private db As Database
Private rs As Recordset

Public Sub userAuth(strUser As String, strPass As String)
Set db = CurrentDb
Set rs = db.OpenRecordset("UserAuthTable", dbOpenDynaset)

rs.FindFirst "[Username] = '" & strUser & "'"

If Not rs.NoMatch And rs![Password] = strPass Then
confirmUser = True
Else
confirmUser = False
End If

End Sub


Public Sub adminAuth(strPass As String)

Set db = CurrentDb
Set rs = db.OpenRecordset("AdminAuthTable", dbOpenDynaset)

If rs![Password] = strPass Then
confirmAdmin = True
Else
confirmAdmin = False
End If

End Sub

Public Sub OpenFrm(stDocName As String)
Dim stLinkCriteria As String

If confirmAdmin Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

If confirmUser Then
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly
End If

If Not confirmAdmin And Not confirmUser Then
MsgBox ("You do not have authorization to view this form! Please Login.")
End If
End Sub
-----------------------------------------------

Once I have the private booleans set I can refer to them like this

If Not getAuth.confirmAdmin Then
SomeAdminControl.Visible = False
End If

Hope that helps.