MS Access 2007 - Identify users and based on that limit access to data

Question

Considering the set up:

• Ms Access Application split into Front End and Back End = both native MS Access
• Front End consists of forms only - it will be the only way to access data
• Front End copy distributed to each user machine (thanks for answers to this question)

I need to implement the following scenario:

• Ms Access application with < 20 users,
• each user is assigned to 1 to 10 projects,
• when user opens the MS Access he should only be presented with the data related to the project(s) he is assigned to

So for example we have users:

• John
• Owen

John is assigned to projects A,B,D. Owen to B,D

When John logs in he can see only data related to projects A,B,D. When Owen logs in he can see only B,D

John and Owen can access the application at the same time

Related tables in the Back End

• user
• project
• userProject - links user(s) to project(s) in many to many relationship.Each user can be assigned to one or more projects, one or more users can work on a project.

I came across this solution on databasedev.co.uk which basically uses a hidden form to store the current users details and then using this to filter the data on other forms.

So here is my Question:

Would that be the recommended solution? Are there any better options? I was thinking that I could use a table on the Front End instead of the hidden form for example.

Answer 1

Edit Re Comment
I see no reason why you should not maintain a table of users in the back-end with a join table of user, project that can be used to filter the projects.

The current user can be obtained with code if you are using network name (http://www.mvps.org/access/api/api0008.htm), it can be stored to a hidden field on the form, which would be useful for setting the form to relevant projects, or you could store the name to a custom database property (http://wiki.lessthandot.com/index.php/Custom_Database_Properties_Creation_and_Use)

The code below applies to finding number of logged-in users.

You could use provider specific ADO Schemas. You need to pass a valid connection, for example:

  ADOUserList Currentproject.Connection


  Public Sub ADOUserList(oConn As ADODB.Connection)
  Dim rs As ADODB.Recordset
      Set rs = oConn.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
      Debug.Print rs.GetString
      rs.Close
  End Sub

More information: http://msdn.microsoft.com/en-us/library/aa155436.aspx

Answer 2

I have a similar, if slightly more complex, situation. In my case, Users are assigned to User Groups, which have varying permissions over Access objects (forms, reports etc). They also have Projects to which they are assigned, and Preferences. Tables:

user

user_group

user_pref

project

user_project

I still, however, use a hidden form (session) which holds session information about the user that's currently logged in. eg: user_id, user_name, subform of assigned projects, preferences (such as 'Current Project').

Finally, a module basSession contains all the functions I need to get or set any of the session information in the hidden form, eg gfSession_GetUserID().

HTH

Answer 3

Be aware that in your current setup there is no way a method to 'Identify users and based on that limit access to data'. If all data resides in a shared backend Access file, your users can just open the back-end database and browse through all data. The only way to actually limit your users' access to the data is by using a database server.

If you want to go to Access I would suggest that you create queries for all (important) tables and use these queries in the forms. Include a WHERE statement in the query that limits the output to what the user may view. You can do this by either changing the complete SQL on opening of the database or include a global variable in the WHERE part of the query and set that variable to the current UserID.