Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
669 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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