?
Solved

User setup, limited to only their department infomation.

Posted on 2013-05-14
10
Medium Priority
?
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 

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 2000 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

Back Up Your Microsoft Windows Server®

Back up 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

719 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