Question Security and user read/write permissions - Access 2010 (1 Viewer)

[VygonDBA]

I'm a SQL Server DBA and have a few Access 2010 Db's to look after and occasionally have to make amendments to (as is the case today). I've been asked to apply permissions to certain users of the Access database whereby users in a group we're calling 'Viewer' only have read permission and users in another group called 'Writer' (original I know!) have both read and write permission.
The database is form driven (i.e. no-one directly accesses and amends the tables in the database), the file is saved with a .mdb extension.

I've played around with the 'Manage Users & Permissions' in the File Menu however these don't seem to do what I need them to and only result in having to input a user and password to access the database on start-up.
I've done some research on Google and for the most part the indications are that the feature I'm looking for is deprecated in Access 2010.

I could easily do this in SQL Server but when it comes to MS Access I'm baffled and would really appreciate some assistance if anyone can suggest anything.

Kind Regards.

 

[CJ_London]

If you are using the 2010 environment (but you do say the db is an mdb) then the built in security has been depreciated and needs to be handled by the developer.

Probably the easiest way to handle this is to create a new table containing network userids (windows logon) and their permissions - in this case sounds pretty basic, they can either read or read/write.

Then create a function which checks the windows logon against the table and then set each forms allow additions and allow edits properties accordingly - and perhaps closes the form if the user is not in the list.

In a module you would have something like

Option Compare Database
Option Explicit
 
Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Obviously this can be built on and refined!
 
Private Function WinUser() As String    ' Get the windows user ID
Dim bfr As String * 100
Dim L As Long
 
    L = GetUserName(bfr, 100)
    WinUser = Left(bfr, InStr(bfr, Chr(0)) - 1)
 
End Function
 
Public Function FormAllow() as Integer
Dim Rst as RecordSet
 
Set Rst=currentdb.openrecordset("SELECT CanWrite FROM tblPermissions WHERE UsrName = '" & WinUser & "'")
If rst.eof then
    FormAllow=1
Else
    FormAllow=rst.fields(0)
End If
Set Rst=nothing
End Function

Then in each form Open event put

Dim Allow as integer
 
Allow=FormAllow
If Allow=1 then
    docmd.closedatabase
else
    me.allowadditions=Allow
    me.allowedits=Allow
End if

Your table tblPermissions would have the following 2 fields:
UsrName - text,indexed, no duplicates
CanWrite - boolean, default false