Wsrtjtyk

I have an Access Database on the SharePoint and two user's roles that have to work on it:

• 
  Role1 is given to a single user. He can manage all the forms of the DB with the possibility to change all the tables. These forms will require many future releases.


• 
  Role2 is given to many users. They can manage only one form that is effective only on a single table of the DB. He doesn't (and mustn't) need to see/manage the others forms/tables (they are a lot). The form used for this role will not require future releases.

What it the best practice to manage this? I thought about the following 2 solutions but probably there is a third one that is better.

Solution #1

Having 2 different Access files linked to the same DB: one with all the forms (that I give to Role1) and one with only one form (that I give to Role2).

Solution #2

Having a single Access file that I give to both the users. Role1 has read/write access to all forms. Role2 can see only a specific form and has no read/write access to other forms.

I would recommend solution #2 otherwise if you change the logic in the front end you have to change it always twice and this is really annoying. Also, if you forget to change both versions and you had a huge change in the logic, you have people working with the old version and people working with the new version.

What I normally do is the following. I have a table tblDeveloperAccess with the ID of your Role1. At the start of the first form load event I have the following code:

Dim db As Database

Dim rs As Recordset



Set db = CurrentDb

Set rs = db.OpenRecordset("tbDeveloperAccess", dbOpenSnapshot)



rs.FindFirst "ID = '" & Environ("USERNAME") & "'"



If rs.NoMatch Then



    DoCmd.NavigateTo "acNavigationCategoryObjectType", "acNavigationGroupTables"

    DoCmd.SelectObject acForm, vbNullString, True

    DoCmd.RunCommand acCmdWindowHide



    DoCmd.ShowToolbar "Ribbon", acToolbarNo



End If

Basically I am hiding the ribbon and the object navigation. You can go even further and disable the context menu and the "Shift + DblClick"-Trick (on your database file). And of course I disable the possibilty on the start form to get into the design view.