Really URGENT- Active Directory to access | Hardcoded value in SELECT stmnt's ADgroup

[Ramya_mudambi]

Dear all,
In Access, via LDAP the system is retrieving the list of users assigned to the specific ActiveDirectoryGroups.

In the SELECT statement below, the lookup values in the WHERE clause has been hard-coded to accommodate nearly 50+ ADgroup names with OR operator via string "name = 'ABCD' OR name = 'EFGH' OR name = 'xyz'" (in this example just 3 ADGroup names have been mentioned)

.CommandText = "SELECT name, member FROM 'LDAP://" & ADServer & "' WHERE " & _
"name = 'ABCD' OR name = 'EFGH' OR name = 'xyz'"
Set rsGroups = .Execute

Earlier instead of using the hard-coded values, in the WHERE clause i have tried referring to column in a table containing these 50+ ADgroup names via string " WHERE name IN (SELECT [ADGroupName] FROM Unique_ADgroup)". The system throws error and stops right at "Set rsGroups = .Execute"

Right now, the AUDITing team is finding it tough to compare the ADgroup names in excel against these hardcoded values of 50+ ADgroup names in VBA string "name = 'ABCD' OR name = 'EFGH' OR name = 'xyz'".

Right now, I'm in a fix and don't how to proceed:
Kindly let me know,
a. How to fine tune the SELECT statement to lookup for ADgroup names from a Table's column instead of hard-coded values
OR
b. Easier way to compare the ADgroup names in excel against these hardcoded values of 50+ ADgroup names in string "name = 'ABCD' OR name = 'EFGH' OR name = 'xyz'"

As this forum is my ONLY ray of hope. Request you to prioritize this and Please help.

Many thanks,
Ramya

 

[JHB]

If you can't get the sub query to run, then I think putting all of the values from the [ADGroupName] in a string variable would work.
Use a loop to add them into the string variable.

 

[Ramya_mudambi]

Bob,
The intension is not to have the hard-coded values in the code at all and instead lookup from a specific table's column.

Please provide your valuable guidance. Tnx

 

[namliam]

You seem to simply want all groups?
Why not simply do:
"SELECT name, member FROM 'LDAP://" & ADServer

??

As this forum is my ONLY ray of hope. Request you to prioritize this and Please help.
If you want priority and expect an answer within 5 minutes, I suggest you get yourself some payed service.

 

[Galaxiom]

You seem to simply want all groups?
Why not simply do:
"SELECT name, member FROM 'LDAP://" & ADServer

??

This is really a continuation of another discussion.
http://www.access-programmers.co.uk/forums/showthread.php?t=258768

The problem was something about too many records being returned. I know Microsoft changed the limits on Active Directory services and I guess on OPs domain this was a problem.

As this forum is my ONLY ray of hope. Request you to prioritize this and Please help.
If you want priority and expect an answer within 5 minutes, I suggest you get yourself some payed service.

Ditto. Also as I said before you really need to chill a bit.

Priorities of the respondents on this board are generally their own. Many of us do it to expand our own knowledge. I answer curious issues like LDAP difficulties because I want to use it more fluently in my own coding and I seek to learn from your experience.

As such it is very much a give and take which is why I appreciated the feedback you gave in your environment. What I was really doing at the time was cleaning up code modules which were written when I barely understood anything.

However your remaining problems in achieving your goal are not so interesting. You just need to learn how to loop through a recordset and get to understand basic coding principles.

Good luck with your project.

 

[Ramya_mudambi]

Dear Sir,
My apologies if my statement "request you to prioritize" irked your sentiments; from the bottom of my heart, i have not written this in the demanding mode. It was more on a requesting/pleading.

Definatly sir, I'm gradually learning and i have tried good amount of permulations in terms of lookup values.

You are correct, this is the continuation of my earlier response and i was not sure if the earlier thread was marked as closed (if it would be seen by others to give response) and hence created a new thread.
Also, the earlier It was no doubt, extremely helpful and the timeline was tight and this was the reason for the push and I'm really thankful for your help.

 

[namliam]

You might be aided with some code like... pseudocode:

SELECT [ADGroupName] FROM Unique_ADgroup
Set rsGroups = .execute
Do while not rsGroups.eof
    .CommandText = "SELECT name, member FROM 'LDAP://" & ADServer & "' WHERE " & _
"name = '" & rsGroups!ADGroupName & "'"
    Set rsName = .execute
    rsgroups.movenext
loop

 

[Ramya_mudambi]

Extract USERS from Nested Active Directory groups into Access

Dear Sir,
I managed to update the code to lookup from the table in access and then extract these specific ADgroups and Users associated to this Adgroup (without hard-coding the ADgroup Names in code). This is working fine now

ATTACHED IS THE UPDATED DATABASE

Here are the updates:

With objCommand1
StrText = GetText()
.ActiveConnection = objConnection
.CommandType = adCmdText
.CommandText = StrText
Set rsGroups = .Execute
End With

Then a small FUNCTION which prepares the SQL SELECT Statement from the table in Access, which looks like:
Private Function GetText()
Dim DB As DAO.Database, RsAdGroup As DAO.Recordset, StrText As String
Set DB = CurrentDb
Set RsAdGroup = DB.OpenRecordset("SELECT ADGroupName FROM Unique_ADgroup;")
StrText = "SELECT Name, member FROM 'LDAP://" & ADServer & "' WHERE Name='Dummy'"
Do While Not RsAdGroup.EOF
StrText = StrText & " OR Name='" & RsAdGroup.Fields("ADGroupName").Value & "'"
RsAdGroup.MoveNext
Loop

GetText = StrText
End Function

------------------

Sir, from observation there appears that there are NESTED groups within each ADgroup. Hence i updated the code from " objectCategory='user' " to " objectCategory='group' or objectCategory='user' " as below
.CommandText = "SELECT sAMAccountName" _
& " FROM 'LDAP://" & ADServer & "'" _
& " WHERE objectCategory='group' or objectCategory='user'" _
& " AND CN='" & strUser & "'"

If the ADgroup name i searched was "dnagsCompadm", the code is extracting the List of GroupNames (under "dnagsCompadm") and Users in "dnagsCompadm". This was ONLY in the client DEMO I realized there were nested Groups and Users belonging to these nested groups were not displaying

Request you to kindly let me know how we can extract Users from the NESTED GroupNames under a specific ADgroup.

I would like to take this opportunity to heartily acknowledge, Within this very short span of time i have managed to gain good amount of knowledge in Access.
Thank you for your continuous guidance and inspiration, programming used to scare me, but now it appears to be nice

Thanks again Sir.