URGENT - Retrieve Users From Active Directory Group, Access 2007 (1 Viewer)

Ramya_mudambi

Registered User.
Local time
Today, 15:14
Joined
Dec 27, 2013
Messages
32
Dear all,

As a beginner with Access, I have spent a lot of time searching this issue but cannot find an answer that works... So I am hoping that by posting my request, your great minds will definitely help me out...!!!!

This is Really Really URGENT - From MS Access, I'm trying to Retrieve USERS from Active Directory Group.

Good news is I'm successful in connecting to the Active directory and retrieve the values for Attributes "Name", "ObjectCategory"
Note: Attributes "Name", "ObjectCategory" - are fields that contain records ONLY in single row.

Where am i Struck:
When i try to extract LIST OF USERS present in "MEMBER" attribute, the system throws "Type Mismatch" error in line - rs!member = objrecordset.Fields("member").Value.
* Resources say, the "member" attribute is a multi-value attribute that contains the list of distinguished names for the user, group, and contact objects that are members of the group

With my limited knowledge, I don't know how to get this field into Access

With your expertise on Active Directory to Access, Request you to kindly help me update the code (below) to extract values in "member" attribute.
Attached is the snapshot (.jpg) from the Active Directory.

Eager to hear from you. SOMEONE PLEASE Shower SOME MERCY AND HELP ME !!

Eager to hear more from you.

Many Thanks,
Ramya

==============================
Option Compare Database

Private Sub Command1_Click()

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
UsrName = InputBox("Enter Domain\UserID:")
Pswd = InputBox("Enter Password:")
oConnection1.Properties("User ID") = UsrName
oConnection1.Properties("Password") = Pswd
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
Dim rs As ADODB.Recordset
Dim strSQL As String

objCommand.CommandText = _
"SELECT Name, objectCategory, member FROM 'LDAP://<IPaddress>:<Port#>' WHERE name='Domain Admins'"

Set objrecordset = objCommand.Execute
If objrecordset.EOF And objrecordset.BOF Then
MsgBox "No records found."
objrecordset.Close
Exit Sub
End If
objrecordset.MoveFirst

strSQL = "Select * from Users_In_ADGroup"

Do While Not objrecordset.EOF
Set rs = New ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Debug.Print rs.Fields("Name")
rs.AddNew
rs!ADGroup = objrecordset.Fields("Name").Value
rs!objectCategory = objrecordset.Fields("objectCategory").Value
rs!member = objrecordset.Fields("member").Value

rs.Update
rs.Close
Set rs = Nothing

End Sub
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Jan 20, 2009
Messages
12,849
Welcome to AWF Ramaya. You have made a big leap forward in solving your problem by coming here.

However you really must try to keep calm. It can be distressing but we developers too frequently find ourselves in difficult circumstances to let it put us into such a self damaging state of mind.

Moreover the distress can impede your ability to communicate the problem.

Fortunately we do know about this kind of thing here. Unfortunately I don't have the code to retrieve the Group information handy at the moment. How tight is your deadline for this requirement?

In the meantime get familiar with LDAP by reading post 13 and on at this thread. It shows how to retrieve other information about the user. Group information is in a multivalue field so there is a bit more code to read that.

Follow the links to more information. You will see that the code required is actually quite a lot simpler than you thought. You might even get enough of an understanding to go the whole way before I get back with the code I have.

At worst, you will have the full code you need early next week so take a deep breath and relax.
 

Ramya_mudambi

Registered User.
Local time
Today, 15:14
Joined
Dec 27, 2013
Messages
32
Thank you, Thank you. Extremely glad that, i have received a response :)
I've been looking for assistance from various forums on this topic for the past 2+ weeks with no responses/solution :(

Tuesday (31-Dec) is the deadline to deliver this.

In whatever I've googled till now, I haven't successful in finding solution for retrieving "multi-value attribute" :(

I'm crossing my finger to get a first glance of the output from this "member" attribute field.

Eager to hear more from AWF team.

Many thanks in advance,
Ramya
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Jan 20, 2009
Messages
12,849
Looking at your code I can see where it went wrong. You were on track in the first part.

I have found the code I used for listing group membership. It is very specific to its task so I will modify it for general use as a function.

Could you tell me exactly how you are wanting to use the group membership data? For example, as a listbox, form recordset etc. Then I will be able to provide the most appropriate return from the function.
 

Ramya_mudambi

Registered User.
Local time
Today, 15:14
Joined
Dec 27, 2013
Messages
32
Dear sir,
My thought was to obtain the data "GroupName" and associated "Users" from Active Directory and display it in Access. The option that struck to my mind was to APPEND/Update to table.

I have NO specifications on how we get it. All i want is to FETCH the data :)

It would be really great if you cold share a working code, I would be really thankful.
This been nearly 3weks and I'm totally struggling sir :(

Many many thanks in advance,
Ramya
 

Ramya_mudambi

Registered User.
Local time
Today, 15:14
Joined
Dec 27, 2013
Messages
32
Dear Sir,
I wanted to-be further clear on what i meant in my earlier reply:

a. There are nearly 40 AD groups which are in access's TableA -> "UniqueADgroup" column. For this earlier, i used the the IN clause in the SELECT statement below; But the code stops right at the "Set objrecordset = objCommand.Execute" statement and ended up testing with hard-coding and lookingup against a specific individual value.

objCommand.CommandText = "SELECT name, member FROM 'LDAP://<IPaddress>:<Port#>' WHERE name IN (SELECT [ADGroupName] FROM Unique_ADgroup)"

b. The code should lookup for these ADgroupNames (Attribute - NAME) and fetch the users (available in Attribute MEMBER) assigned to these ADgroups

The final output should list: "ADgroupNames" and users assigned to these ADgroups. Example below:

ADgroupNames Users
----------------- -------------
Domain Admins _QuestRMAD
Domain Admins _QuestARS
Domain Admins _QuestRptr
Domain Admins DR_Admin
Domain Admins T705008
Domain Admins _nabackup
Domain Admins _QuestGPOAdmin

Sir, I'm extremely eager to hear and see the code you would be sharing. Be it a function, procedure, something that you're willing to help me sail this ship :(

Thanks a ton,
Ramya
 

Ramya_mudambi

Registered User.
Local time
Today, 15:14
Joined
Dec 27, 2013
Messages
32
The result should be populated to a table in Access in 2 columns - "ADGroup Name" and "Users"

Apologies, out of anxiety i wrote too many responses :(. Sorry, if this has irked your feelings.

Crossing my fingers to hear more from you.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Jan 20, 2009
Messages
12,849
I have not yet had an opportunity to build a fully featured function.

Meanwhile this is the crucial part about querying the member field which is stored as an array. Hopefully you will be able to comprehend it enough to graft the concept into your code.

Code:
oCommand1.CommandText = "SELECT name, member from 'LDAP://" & ADServerName & "' WHERE objectCategory='Group'"
Set ADGroups = oCommand1.Execute

With ADGroups
.MoveFirst

Do While Not .EOF

    If !name <> "IIS_WPG" Then
    
        strGroup =!name
    
        If IsArray(!member) Then
            arrMembers = !member
    
            For Each varMember In arrMembers
                 {use varMembers and strGroup to update a recordset or construct an SQL command}
            Next
        End If
    End If

    .MoveNext
Loop

End With
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Jan 20, 2009
Messages
12,849
I hope you have found the previous post set you in the right direction.

However I wanted to continue to discuss how you are using the data. You very well described that you want it in a table but you didn't actually say how the data will be interacting with the user.

The tables in Access are just once source of data for forms and reports. Data from the Active Directory database are just as readily a source of data too, without having to load that data into Access at all.

A recordset returned from an LDAP query can be used to populate a form or listbox. An ADO recordset can be disconnected from the original data source and held locally in RAM without further contact with the host being required. Moreover that data is never written to a file eliminating much of the need for synchronising data into temporary tables.

The recordset in RAM can be used as the basis of a function allowing the program to retrieve the information at any time without loading the server.

The scope of handling external data is so broad that it is really important to know what you are doing with the data. How the user and the data interact with your application is more important than what you perceive is the to best way to do it. A table might not be what you need.

I hope that makes sense.
 

Ramya_mudambi

Registered User.
Local time
Today, 15:14
Joined
Dec 27, 2013
Messages
32
Dear Sir,
Regarding how i would use this data:
I'm working on developing a reporting Database, where we have a more than 30 Systems like AS400, BASIS, etc.,
As an Example: For AS400 system,
1. We have been provided a manual extract of users and the role they are assigned to
2. We're supposed to extract the list of users to a specific set of ADgroups

Now, we're supposed to combine the result set of 1 & 2 as the final result for AS400 system

This was the reason, i was looking for the "ADgroups" and associated "Users" tobe available in a Table.

I update the code shared by you (see below for your ref).
At line - "If !Name <> "IIS_WPG" Then" - The system throws, Compile error (Invalid or Unqualified reference)

Respected sir, I'm just left with a day's time (With my very limited knowledge (zero) on VBA) and I'm totally clueless on how to have the code working.

You're my last ray of hope. I'm pleading you to help me out :( :(

========================================
Private Sub Command2_Click()
Dim varInfo As Variant
varInfo = ""
Dim QI As Integer
QI = MsgBox("Recreate Table?", vbYesNo)
If QI = 6 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "DeleteUsers"
DoCmd.SetWarnings True

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
UsrName = InputBox("Enter Domain\UserID:")
Pswd = InputBox("Enter Password:")
objConnection.Properties("User ID") = UsrName
objConnection.Properties("Password") = Pswd
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
Dim rs As ADODB.Recordset
Dim strSQL, strGroup, varMember As String

objCommand.CommandText = "SELECT Name, member FROM 'LDAP://161.162.201.43:389' WHERE name = 'Domain Admins'" 'SELECT ADGroup_Name FROM Unique_ADgroup)"

Set objrecordset = objCommand.Execute
If objrecordset.EOF And objrecordset.BOF Then
MsgBox "No records found."
objrecordset.Close
Exit Sub
End If
objrecordset.MoveFirst

strSQL = "Select * from Users_In_ADGroup"

Do While Not objrecordset.EOF
If !Name <> "IIS_WPG" Then

strGroup = !Name

If IsArray(!member) Then
arrMembers = !member
For Each varMember In arrMembers
Set rs = New ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!ADGroup = objrecordset.Fields("strGroup").Value
rs!member = objrecordset.Fields("varMembers").Value
Next
End If
End If
rs.Update
rs.Close
Set rs = Nothing

'MsgBox varInfo
objrecordset.MoveNext
Loop
MsgBox "Table made - Press OK to view table."
DoCmd.Minimize
DoCmd.OpenTable "Users_In_ADGroup"
Else
DoCmd.Minimize
DoCmd.OpenTable "Users_In_ADGroup"
End If
End Sub
========================================
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Jan 20, 2009
Messages
12,849
You're my last ray of hope. I'm pleading you to help me out :( :(

Don't panic. You will have your code in time.

Unfortunately I am slightly disadvantaged because I am not at work and I don't have a domain at home. However I am sure any remaining problems will be very minor and at worst I will be at work tomorrow where I will be able to test the code.

Thanks for posting where you are with the code. It is probably easier for you to follow where you have been before. I have edited it to what I think is correct but as I said I can't test. Note the code was edited in the AWF posting window so it is untested and it might have some typos.

I have also changed the delete query to run under CurrentDb. This removes the need to turn off Warnings. Moreover, including dbFailOnError as the second argument will throw an error if there is a real problem. If it gives you any problems then change it back to what you are comfortable with.

I also converted one group of properties to a With group as an example. You really should get into these as it makes the code much easier to write and read.

Code:
Private Sub Command2_Click()

Dim varInfo As Variant
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim strGroup as String
Dim varMember As Variant
Dim QI As Integer

QI = MsgBox("Recreate Table?", vbYesNo)

If QI = 6 Then
    Currentdb.Execute "DeleteUsers", dbFailOnError

    Set objConnection = CreateObject("ADODB.Connection")
    Set objCommand = CreateObject("ADODB.Command")

    UsrName = InputBox("Enter Domain\UserID:")
    Pswd = InputBox("Enter Password:")

    With objConnection
          .Provider = "ADsDSOObject"
          .Properties("User ID") = UsrName
          .Properties("Password") = Pswd
          .Open "Active Directory Provider"
    End With

    Set objCommand.ActiveConnection = objConnection

    objCommand.CommandText = "SELECT name, member FROM 'LDAP://161.162.201.43:389' WHERE objectCategory = 'Group'"

    Set objrecordset = objCommand.Execute

    If objrecordset.EOF And objrecordset.BOF Then
        MsgBox "No records found."
        objrecordset.Close
        Exit Sub
    End If

    Set rs = New ADODB.Recordset
    strSQL = "SELECT * FROM Users_In_ADGroup"
    rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

   objrecordset.MoveFirst

    Do While Not objrecordset.EOF

        If objrecordset!name <> "IIS_WPG" Then
            strGroup = objrecordset!name

            If IsArray(objrecordset!member) Then
                arrMembers = objrecordset!member

                For Each varMember In arrMembers
                    rs.AddNew
                    rs!ADgroupNames = strGroup
                    rs!Users = varMembers
                    rs.Update
                Next

            End If
        End If

        objrecordset.MoveNext

    Loop

    rs.Close
    Set rs = Nothing

    MsgBox "Table made - Press OK to view table."
    DoCmd.Minimize
    DoCmd.OpenTable "Users_In_ADGroup"

Else
    DoCmd.Minimize
    DoCmd.OpenTable "Users_In_ADGroup"
End If

End Sub

Good luck. I will be in touch tomorrow to fix any glitches that might remain.
 
Last edited:

Ramya_mudambi

Registered User.
Local time
Today, 15:14
Joined
Dec 27, 2013
Messages
32
Thank you so much for the confidence Sir,

Copy/pasted the code and when i executed the code,
At line - "If !Name <> "IIS_WPG" Then" - The system throws, Compile error (Invalid or Unqualified reference)

Sir, I will WAIT for your inputs with the final working code. If you don't mind, would it be possible for you to share the DB where the code executed successfully.

Once again, really grateful for all the timely help :)

I noticed, you have used WHERE name = 'Group'. Pardon my silly question, Is there a way, we can lookup from a specific table's column? Something like, WHERE name IN (SELECT [ADGroup Name] FROM Unique_ADgroup). Earlier, i'd tried this and the query did not execute in the script but IN clause works fine in regular query.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Jan 20, 2009
Messages
12,849
At line - "If !Name <> "IIS_WPG" Then" - The system throws, Compile error (Invalid or Unqualified reference)

I was still fixing a couple of issues after I posted. You got a version before i had fixed that reference. You must have got straight on after I posted as I had the edit completed in a few minutes.

If you don't mind, would it be possible for you to share the DB where the code executed successfully.

That database handles a very specific task where I work. The LDAP query is a tiny part of the code. Most of it is managing other databases on SQL Server so it is not relevant to your case.

I noticed, you have used WHERE name = 'Group'.

That was also from before I had fixed the errors in the post. Try copying the code from the site again.

Is there a way, we can lookup from a specific table's column? Something like, WHERE name IN (SELECT [ADGroup Name] FROM Unique_ADgroup). Earlier, i'd tried this and the query did not execute in the script but IN clause works fine in regular query.

The Active Directory database is quite separate from Access. The LDAP query does not know about tables in Access and vice versa.

Once we get the Sub working, your table will receive a list with records for each member in each group. Users with multiple group memberships will have a record for each group they are in.

Your unique groups then becomes a query rather than a table.
SELECT DISTINCT ADGroupNames FROM Users_In_ADGroup

To query the members in a list of groups would require a separate LDAP query in a loop for each item in the list. Then a loop through the members field to extract the usernames from the array.

It would be far more complex than simply making a table of the group versus username. Once that data is in Access it is quite easy to work with. Getting the list of users in a particular group would be a simple query,
 

Ramya_mudambi

Registered User.
Local time
Today, 15:14
Joined
Dec 27, 2013
Messages
32
Thank you for the clarification regarding the WHERE name = 'Group'

I tested the code right now and the error is at line "rs!ADgroupNames = strGroup".
Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or control.

I guess once you're at work, you will be able to perform complete testing and bug fix the errors.

Sorry to bother you, I will wait to hear more from you. Thanks Captain.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Jan 20, 2009
Messages
12,849
I tested the code right now and the error is at line "rs!ADgroupNames = strGroup".
Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or control.

I got the ADGroupName fieldname from your earlier post
The final output should list: "ADgroupNames" and users assigned to these ADgroups. Example below:

ADgroupNames Users
----------------- -------------
Domain Admins _QuestRMAD

If you have different field names in the destination table then change the names of the rs recordset fields in the code to match them.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Jan 20, 2009
Messages
12,849
Here is a sample database. Note the Constant at the beginnng of the module to define the server name or address.

The previous code I supplied returns the full DN of the users in the group.

In this one I have added the username and login too. I wasn't sure exactly what you needed. You may need to adjust the code to match your table and what you want in the fields.
 

Attachments

  • AD.zip
    18 KB · Views: 305

Ramya_mudambi

Registered User.
Local time
Today, 15:14
Joined
Dec 27, 2013
Messages
32
When i executed the SELECT statement as, "WHERE objectCategory = 'Group'"; the system threw Run-time error '-2147016669 (80072023)': The size limit for this request was exceeded.

I tuned the SELECT statement to: WHERE name = 'Domain Admins' and it worked like a CHARM.

Master, Master... You're the best :)

You've kept up your promise in helping me and you did.
Thanks a ton GURUji

Many Many thanks for the timely help :)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:44
Joined
Jan 20, 2009
Messages
12,849
When i executed the SELECT statement as, "WHERE objectCategory = 'Group'"; the system threw Run-time error '-2147016669 (80072023)': The size limit for this request was exceeded.

That is good to know. My domain has far fewer groups so I asume that it is the number of groups that is the issue. I will look into that sometime. LDAP certainly is complex.

So to get the member list of all the groups I guess you would have to work through your list of groups and run the query for each one.

This could be done by separating out the query section into a function which accepted the group name as an argument and passing that to the query.

BTW How did you get your original list of groups in the table?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:44
Joined
Aug 30, 2003
Messages
36,118
FYI, thread moved from introductions.
 

Ramya_mudambi

Registered User.
Local time
Today, 15:14
Joined
Dec 27, 2013
Messages
32
Sir,
for now, in the SELECT query i'll have to use WHERE name = 'Domain Admins' or name = 'administrator*'
 

Users who are viewing this thread

Top Bottom