CODEWITHSUNDEEP
ms-accessauthenticationauthorizationms-access-2007
kristof
kristof

Reputation: 53824

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

Considering the set up:

I need to implement the following scenario:

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

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.

Upvotes: 0

Views: 4627

Answers (3)

Birger
Birger

Reputation: 4353

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.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91306

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

Upvotes: 1

maxhugen
maxhugen

Reputation: 1944

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

Upvotes: 1

Related Questions