CODEWITHSUNDEEP
databasems-access
Penny Lane
Penny Lane

Reputation: 1

How to make Sensitive Data viewable to a select few on Microsoft Access

I am currently working on a database for the National Park which consists mostly of attached documents. This database will be viewable to all on the Shared S drive.

My Access DB has a field called Sensitive with a Yes/No checkbox. Is there a way for those documents that are sensitive to only be viewed by some. Is a password possible on those?

Upvotes: 0

Views: 57

Answers (3)

JennyW
JennyW

Reputation: 106

I agree with JCro, and have used something similar as I'm creating a system for a small company who have no AD, so I can't use AD groups to control access.

I've created an employees table to hold their basic contact info, and also a Role, eg Admin, Manager, Supervisor, Standard. I have sensitive data, such as the employee data, that is only accessible to anyone in the Manager or Admin roles, and as JCro has suggested, I use Dlookup to check what role a user has and allow access or provide a message explaining they don't have permission to access the section they have selected.

I use this code to capture their windows login name, slightly different to JCro but the same result - 

Private Declare Function APIGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetUserName() As String

'Returns the network login name

Dim lngLen As Long
Dim lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = APIGetUserName(strUserName, lngLen)
If (lngX > 0) Then
    GetUserName = Left$(strUserName, lngLen - 1)
Else
    GetUserName = vbNullString
End If
End Function

Upvotes: 0

Gustav
Gustav

Reputation: 55921

If the files are stored on a network share with access for all users, there is no way in Access or other tools to limit that access.

But access could be controlled with Active Directory. Assign the users to different groups, and grant different rights to the individual files for these groups.

In Access you can read the group of the current user, and then prevent (with no errors) that the user reads, creates, or deletes files on the network share.

Upvotes: 1

JCro
JCro

Reputation: 696

You can have a table with a list of usernames able to access the sensitive files. You can then check the windows username against that table.

This will return the windows username (paste it into a module of course):

Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" (ByVal lpName As Long, ByVal lpUserName As String, lpnLength As Long) As Long


Public Function GetNewLogin() As String
    Dim lRetVal As Long
    Dim sName As String
    Dim i As Long

    sName = Space(50)

    lRetVal = WNetGetUser(0, sName, 50)
    i = InStr(sName, Chr(0))
    If i > 1 Then
        GetNewLogin = Left(sName, i - 1)
    End If
End Function

Then you can simply use DLookup() or DCount() to check against your table. If you want multiple levels of access, then you should add a 'Role' field to your table of usernames, and check against that.

Upvotes: 0

Related Questions