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
640 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

821 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