• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • Last Modified:

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

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
LisaValbuena
Asked:
LisaValbuena
  • 5
  • 4
1 Solution
 
irudykCommented:
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
 
dannywarehamCommented:
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
 
LisaValbuenaAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
irudykCommented:
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
 
LisaValbuenaAuthor Commented:
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
 
irudykCommented:
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
 
LisaValbuenaAuthor Commented:
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
 
irudykCommented:
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
 
LisaValbuenaAuthor Commented:
Yes ...!!!!

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

THANKS ... THANKS ... THANKS!

0
 
LisaValbuenaAuthor Commented:
Wonderful ... I appreciate your help.

Sincerely,

EL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now