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
630 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
  • 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

896 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

16 Experts available now in Live!

Get 1:1 Help Now