Solved

How do I find out the Active Directory user group a user belongs to from MS Access 2003

Posted on 2008-10-09
10
665 Views
Last Modified: 2012-05-05
From MS Access 2003 and VBA, how do I find out a user accounts details, such as the user group they belong to, so that I can customize which menu options are available to them, without having to create another set of passwords?

By using Windows API calls, I alredy know how to figure out the username, but I am missing the user group from an Active Directory, Windows 2003 Server.
0
Comment
Question by:LisaValbuena
[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
  • 5
  • 4
10 Comments
 
LVL 23

Expert Comment

by:irudyk
ID: 22684460
You could alter the following to work for you.  The function will show in the immediate window the list of AD groups a user belongs to.
Function GetUserGroups(strUserID As String)
 
Dim conn As Object
Dim rs As Object
Dim user As Object
Dim group As Object
 
Set conn = CreateObject("ADODB.Connection")
conn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject"
 
Set rs = conn.Execute("<LDAP://DC=tl,DC=localhost>;(&(objectCategory=person)(objectClass=user)(sAMAccountName=" & strUserID & "));adsPath;subTree")
 
If Not rs.EOF Then
    
    Set user = GetObject(rs("adsPath"))
    
    For Each group In user.Groups
        Debug.Print Mid(group.Name, InStr(1, group.Name, "=") + 1)
    Next
 
End If
 
Set group = Nothing
Set user = Nothing
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
 
End Function

Open in new window

0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22693972
You can create a user table in access as use their PC login as a means of restricting their usage:
http://www.mvps.org/access/api/api0008.htm  Login
http://www.mvps.org/access/api/api0009.htm   PC Name


You can also get their directories as so:
http://www.mvps.org/access/api/api0010.htm
0
 

Author Comment

by:LisaValbuena
ID: 22694204
I created the function you wrote about, but when I run it I get an error message:

Run-time rror -2147217865 (800r0e37)
Table Does not exist

Referencing the following line in your code:
Set rs = conn.Execute("<LDAP://DC=tl,DC=localhost>;(&(objectCategory=person)(objectClass=user)(sAMAccountName=" & strUserID & "));adsPath;subTree")

I invoked the function by typing:
? GetUserGroups("Eroth") from the immediate window, "Eroth" being the username connected to the Windows 2003 Network.

Please advise.

Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:irudyk
ID: 22695395
You will need to change the
    DC=tl,DC=localhost
to the particular domain components - e.g.
    DC=YourDomain,DC=YourCompany
of
    DC=YourDomain,DC=com
0
 

Author Comment

by:LisaValbuena
ID: 22698524
Thanks for responding and trying to clarify the issue.

I followed your suggestions and replaced the line in question to:

Set rs = conn.Execute("<LDAP://DC=xxxx.local,DC=XXXX>;(&(objectCategory=person)(objectClass=user)(sAMAccountName=" & strUserID & "));adsPath;subTree")

Where xxxx.local is the name of the Domain - I opened the Active Directory Users and Computers - Admininstrator and read it from there.

XXXX Company Name

Still it gives me an error message, not being able to find the table.

Is there a particular syntax that I must follow?

Or how do it find out the correct Domain Name and Company Name spelling as stored in the Active Directory?

As said before, I went to the Active Directory Users & Computers - Management Console screen

Please advise,

Thanks
0
 
LVL 23

Expert Comment

by:irudyk
ID: 22698827
Okay, in the Active Directory Users & Computers console screen, on the left side you should see at least a couple of folders under Active Directory Users & Computers.  The first should be Saved Queries, the next should be the DC information you are looking for.
So, if that folder is named
ABCD.EFGHI
then use
DC=ABCD,DC=EFGHI
If that folder is named
ABCD.EFGHI.JLK
then use
DC=ABCD,DC=EFGHI ,DC=JKL
0
 

Author Comment

by:LisaValbuena
ID: 22705406
irudyk:

Thanks ... We are making progress and I was successful in replacing the DC references, as per your suggestions.

The problem I have now, is that if I pass the string in quotes such as:

GetUserGroups("Eroth") it works fine.

But the goal is to replace the username dynamically.

I invoke a Function called GetCurrentUsername() which returns a string, which in turn I should pass to the GetUserGroup function. you are sharing with me.

I have tried several options, enclosing the returned string in quotes, changing the GetUserGroup reference to ByVal, ByRef but I cannot make it work.

Please advise

THANKS in advance.
GetCurrentUserGroupIssue.doc
0
 
LVL 23

Accepted Solution

by:
irudyk earned 500 total points
ID: 22705835
The line in your Form_Load() that is
    strUserId = strQuote & Left(strUserId, iLen) & strQuote
should be changed to
    strUserId = Left(strUserId, iLen)
When I did that and tested your code I got the user group information to return correctly.
0
 

Author Comment

by:LisaValbuena
ID: 22705930
Yes ...!!!!

As I was testing, prior to changing the Function to be ByVal, I was enclosing the string in quotes.

THANKS ... THANKS ... THANKS!

0
 

Author Closing Comment

by:LisaValbuena
ID: 31505662
Wonderful ... I appreciate your help.

Sincerely,

EL
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

615 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