Secure Database

For lurkers...

Yes, this can be done. It's not automatic however, but not that difficult if you're a programmer. You'll have to do this with code. Usually one would have more than one table to do this. For example, a Users table and a DivisionInfo table. You use the Users table to track, grant, and control rights.

When my users open an Access database, they don't login to MS Access using the security built into MS Access.  They have already logged into the network and workstation with their credentials, so I just use those.

I use a vba function to determine their windows username.  Then I look up that username in my table of rights/restrictions and enable/disable whatever I need based on who they are.

I keep them captive in forms (or reports).  It GREATLY reduces problems.

Also, it is a good practice when multiple people share a networked access database to separate your GUI from your Data by splitting the file.  It greatly reduces the chance of file corruption and unlocks the GUI for development without having to lock everyone out while you work!

8^D

Darron

P.S.  I almost forgot to add the vb code.  Here it is...

Option Compare Database
Option Explicit

' In the Declarations section
Declare Function GetComputerName& Lib "kernel32.dll" _
                         Alias "GetComputerNameA" (ByVal lpBuffer As String, _
                         nSize As Long)                                        ' Network CPU Name

Declare Function GetUserName& Lib "advapi32.dll" _
                        Alias "GetUserNameA" (ByVal lpBuffer As String, _
                        nSize As Long)                                         ' Network login name

Public Const MAX_COMPUTERNAME_LENGTH = 15                        ' Varies with platform OS
Function sGetUser() As String                             ' Returns current network logon
'   Usage:      ="Network User Name: " & sGetUser()
    Dim s$, cnt&, dl&, sWho As String
    cnt& = 199
    s$ = String$(200, 0)
    dl& = GetUserName(s$, cnt)
    sGetUser = Left$(s$, cnt - 1)
End Function

Function sGetComputer()                                   ' Returns network name of computer
'   Usage:      ="Computer Name: " & sGetComputer()
    Dim s$
    s$ = String$(MAX_COMPUTERNAME_LENGTH + 1, 0)
    Dim dl&
    Dim sz&
    sz& = MAX_COMPUTERNAME_LENGTH + 1
    dl& = GetComputerName(s$, sz)
    sGetComputer = Left$(s$, sz)
End Function

Public Function sGetUserID()                              ' Currently (Access) logged user
'   Usage:      ="Access User ID: " & sGetUserID()
    sGetUserID = CurrentUser()
End Function

Function getlevel()
    Dim rstUsers As ADODB.Recordset
    Set rstUsers = New ADODB.Recordset
    Dim ulevel As String
    Dim errLoop As Error
    Dim selstr As String
    selstr = "SELECT username, accesslevel FROM users where username = '" & sGetUser() & "'"
    rstUsers.Open selstr, CurrentProject.Connection
     On Error GoTo linenext
    ulevel = rstUsers.Fields("AccessLevel")
linenext:
    rstUsers.Close
    Set rstUsers = Nothing
    getlevel = ulevel
   
End Function