Solved

User setup, limited to only their department infomation.

Posted on 2013-05-14
10
176 Views
Last Modified: 2013-05-29
I have an MS Access 2010 database that I would like users to access, the problem is I would like each user to see only their department information.  Can someone point me to a code that I can use for this.  I am desperate.  

Thanks
0
Comment
Question by:Kdankwah
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39166457
Hi,

Try these steps:

1. Create a table (tUsers) with users names, userids and departments.
2. Get the users network userid with the following code:

new module...

Option Compare Database
Option Explicit
Declare FunctionAPI_GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetUserName() As String
   
    Dim lpBuf As String
    Dim ret As Long
   
    lpBuf = String(255, 0)
    ret = API_GetUserName(lpBuf, Len(lpBuf))
    GetUserName = UCase(Left(lpBuf, InStr(lpBuf, Chr(0)) - 1))
   
End Function

... end of module


3.  On your forms/reports join the user table tUser with the department selected by the userid = GetUserName()

Regards,

Bill
0
 
LVL 21
ID: 39166689
Can someone point me to a code that I can use for this.

It takes a lot more that just code to do this. Every query must be updates to filter the records. If form's or report are based on tables then they will have to be updates to use a query that filters the records.

Implement this type of security in Access is something that should be done at the very beginning. It needs to be integrated into everything. It is possible to bolt in onto an existing Access Application but it is a lot of work and possible redesign.

Do you already have user tracking created?
Does your table design and relationships allow you to easily filter everything by department?
0
 

Author Comment

by:Kdankwah
ID: 39168028
Thanks TheHiTechCoach, the database will have only one table and will not be updatable by the users.  Its going to be read only and for informational purposes.  

I will update the database at the backend.  Its only for users to see their reports.  

I really understand your concern if users were going to update the database.  


Thanks
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39168193
Create a public variable

public glbDept as string '--- or Long depending on what type of field the dept is.

Now, create a funcion to return this value so you can refer to it ANYWHERE...

FUNCION get_glbDept () as string '---- or long depending on what type....
get_glbDept = glbDept
End Function

Create a logon routine that looks up your users and determines their dept (might be a homemade routine).
Assign the User's department to the public variable:    glbDept = {Users Department}



Now, if you are binding your forms straight to tables... make queries instead.  In the query design, in the DEPT column....     = get_glbDept()   for criteria.

Scott C
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39168201
Further.... Except for VBA code, you cannot reference a variable value straight up.  But.... you can reference a FUNCTION that returns the value.   Using this technique allows you to actually use public variables to control entire systems.

I do it all the time....

Scott C
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Kdankwah
ID: 39168249
What if a user (a Director) is allowed to see two or three departments.  How would you create the code for that?.  

Should I have the Director in the users table multiple times based on what department they are allowed to see or have separate fields for each department, like department 1, department 2 etc.  
Another option I am thinking if doing is to have one department field and add the department numbers separated by comma.

Thanks
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39168843
Create a "child" table to maintain each user's departments (each record is a department number and userid.   Put these in a single query and link this query to your other queries... including all the departments per user.

Instead of public variable for the dept.... do the same thing for the UserID.  Put the "=get_userid()" function in the query that marries both the userid and table of departments.

Scott C
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39168960
Hi,

As Scott says it's not too difficult but you will need to set up the process.

Here is an article (from a different Scott) that might give you some insight.

http://www.accesssecurityblog.com/author/Scott-Diamond.aspx

Regards,

Bill
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 39169295
Kdankwah,

It really does not matter it you have one table or many table. Or if the data is read-only or updatable. The security model will be the same.

I assume that your main table has a field to identify the department. Like DeptID.

Based on what you need you should use security groups.  Similar to the was Windows handles security.

You will have there tables
1) Groups
2) Group Departments
4) Group Users
4) Users
 
For a specific user, to find what departs they can see you would use the following table relationships  to get a list if there available departs.
 
Users -> Group Users - > Group Department

This would be used to filter the records in your main table by Department ID.

It is possible to get the Windows User name and then automatically filter the records.

If you are in a domain and using Active Directory, it may be possible to use the security group in AD to filter the records. Assuming you already has the security model set up in AD.
0
 

Author Closing Comment

by:Kdankwah
ID: 39206009
I like the answer given by TheHitechcoach
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now