LDAP ADO Query (1 Viewer)

modest · Mar 7, 2005

This is what works in my Cmd.exe console:

Code:

ldapsearch -D "cn=Directory Manager" -w password -h directorydev.domain.com -b aid=it0030,ou=Applications,o=domain.com,c=us userreferencedn=*xternal* dn lastaccesstime

It returns something that looks like:
dn: auid=pppep, aid=it0030, ou=Applications, o=domain.com, c=US lastaccesstime: 20040922120851Z
notice the dn and lastaccesstime

I'm trying to run this query in Access to return the same results. So far, I have the following that returns the same amount of records, but not the same output:

Code:

    Dim con As ADODB.Connection
    Dim com As ADODB.command
    Dim rs As ADODB.Recordset
    
    
    Dim ADsPath As String
    ADsPath = "LDAP://directorydev.domain.com/aid=it0030,ou=Applications,o=domain.com,c=us"
[COLOR=Green]  'set the connection[/COLOR]
    Set con = New ADODB.Connection
    With con
        .Provider = "ADSDSOObject"
        '.Properties("User ID") = "cn=Directory Manager" [COLOR=Green]',aid=it0030,ou=Applications,o=domain.com,c=us"[/COLOR]
        '.Properties("Password") = "password"
        .Open "ADSI"
    End With
    
[COLOR=Green]  'set the command object[/COLOR]
    Set com = New ADODB.command
    With com
        .ActiveConnection = con
        .CommandText = "<" & ADsPath & ">;(userreferencedn=*xternal*);ADsPath;subtree"
    End With
     
    Set rs = com.Execute
    
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveLast
        rs.MoveFirst
        MsgBox rs.RecordCount & "'s Returned"
        While Not rs.EOF
            MsgBox rs.Fields(0).Value
            rs.MoveNext
        Wend
    End If
    
    con.Close

My output for this is:

LDAP://directorydev.domain.com/auid=pppep, aid=it0030, ou=Applications, o=domain.com, c=us
notice how lastaccestime is not included, and the LDAP server is

I tried changing
.CommandText = "<" & ADsPath & ">;(userreferencedn=*xternal*);ADsPath;subtree"
to
.CommandText = "<" & ADsPath & ">;(userreferencedn=*xternal*);ADsPath,dn,lastaccestime;subtree"
and I tried
.CommandText = "<" & ADsPath & ">;(userreferencedn=*xternal*);dn,lastaccesstime;subtree"

but nothing works other than ADsPath or *

**Note:
lastaccesst ime (above) = lastaccesstime
sub tree (above) = subtree
the forum has a bug
**

-Thanks to whomever will try to figure this out,
modest

modest · Mar 8, 2005

Okay I know the problem. Now I need to query LDAP.

Code:

The directory structure looks like this:

[COLOR=Blue]o=domain.com,e=us [/COLOR][COLOR=Green]-this is the domain [/COLOR] 
    [COLOR=Blue]ou=Applications [/COLOR][COLOR=Green]{the ou's are a list of folders under the domain} [/COLOR] 
        [COLOR=Blue]aid=it0030 [/COLOR][COLOR=Green] {the aid's are a list of folders under the ou} [/COLOR] 
            [COLOR=Blue]auid=*a user id*[/COLOR] [COLOR=Green]{the auid's are all the users for that folders [/COLOR] 
                [COLOR=Blue]{user attributes} [/COLOR] [COLOR=Green]- these are just attributes of the users last access time is here[/COLOR]

1)lastaccesstime is one of the attributes of auid.
2)I need all the auid's and all their accesstimes from the it0030 folder.
3)these should be external users. the (userreferencedn=*xternal*) works in the code above.

can anyone help me script that? even if listed how you would do it as an SQL would help.
please help!
-modest

modest · Mar 8, 2005

Since no one is replying, I will post some of my findings (please correct me if I'm wrong).

I was doing a bit of reading from various books/sites and I guess that the only return attributes I can receive is ADsPath or Name (cn).

In order to be able to get the other attributes, I need to unlock some property or add something as a parameter. One author, I believe, is leading me in the right direction. He has a 12-part series of articles that I believe anyone looking to query LDAP (or any Active Directory) with VBA should look into. One of his articles located here goes so far to say "In the future, I'll delve further into ADO, looking at the SQL dialect and the extra parameters you can set to control the data that you receive from your searches" .. however this was posted 6 years ago and I've searched all his articles and haven't seem to find his solution

Currently, I'm researching the IADs User Accounts to hopefully point me in the right direction for a solution.

Again, I would appreciate anyone's help.
Thank you,
-modest

ghudson · Mar 8, 2005

modest,

I too would like to be able to build an Access front end that can extract the LDAP info. It would make searching a whole lot easier.

How do you know where to point Access to the LDAP file data?

Are you able to pull a record set out of your LDAP data file? If so, can you provide an example that I can play with?

Thanks!

modest · Mar 9, 2005

Response - on the right track

Hey hudson,

My first post above showed how to connect and extract the adspath to a recordset (it also yielded the user id). IADs appears to be the answer to get the data that I needed. I will look further into this at work tomorrow.

Lastaccesstime and the other values I was looking for are actually properties of the user information, which I feel that I can get using the IADs and IADSproperty objects. To this point, I can get vba to display the titles of the properties however, the values it prints out are numbers it's text that I need. I will look more into this tomorrow as well.

As far as getting a "file", originally I typed the LDAPsearch string in a command window and redirected it to a text file using the msdos redirect character (">"), however VBA doesn't allow redirects, so using shell() was out of the question.

Keep in mind that JavaSDK has it's own browser to actually view the LDAP directory hierarchy, I'm only trying to download this information so that people who aren't as computer-literate can easily run their own queries.

Short Tutorial:
In any case, you will need to build and open ADO connection as I have and upon that you call the query either using an SQL-like call or the LDAP Query format as I have above. The first part (your ADsPath) connects to the folder [LDAP://serveripOrAddress/x=FolderYouWantToSearch,y=NextHigherFolder,...z=highestDirectory(the domain)] note that this path is placed in angled brackets < >. Additionally, note that the lowest folder is first and the folders are separated by commas.. "x" "y" and "z" should be replaced with whatever descriptors your LDAP uses.

The query is the second part and it is placed in parentheses. It is equivalent to the WHERE clause of an SQL statement. For more information on how you might limit it see my post here.The third thing is what you're pulling out of the query, which is equivalent to the SELECT x statement, but the only thing you can pull is name (or "cn") or ADsPath ...and possibly uid (userid),but I'm not sure.

The final part of the LDAP query says which directories you search (what folder is it going to look in...only one level down, all the levels below, no levels below, etc.)

So your final query should look something like:
.CommandText = "<LDAP://directory.domain.com/aid=SubFolder,ou=Folder,o=domain.com,c=us>;(userreferencedn=external*);ADsPath;subtree"

You get the return of this by setting your recordset equal the the command execution (command.Execute) where command is defined as an ADODB.command (see my code from the first post).

The recordset will be populated that looks something like this:
LDAP://directory.domain.com/auid=user1, aid=SubFolder, ou=Folder, o=domain.com, c=us
LDAP://directory.domain.com/auid=user2, aid=SubFolder, ou=Folder, o=domain.com, c=us
LDAP://directory.domain.com/auid=user3, aid=SubFolder, ou=Folder, o=domain.com, c=us

notice what changed: auid, which is what I was searching for in the folder. Later on I will break the code down using mid$() other string functions, to put them in their own fields, for a more presentable report.

I can show you how to get to the properties of the auid, but I will wait until I figure out how to get the exact values I'm looking for. This is because I don't want to stear anyone in the wrong direction if I'm wrong.

Hope this helps,
Modest

modest · Mar 9, 2005

The code I'm currently looking at for property values is on Novell's site at: http://developer.novell.com/support/sample/tids/ildap01/ildap01.htm
There is C at the top and one or two VB examples at the bottom.

Let me know if this was any help.

ghudson · Mar 9, 2005

When I tried your ADODB code from above I am getting a runtime error that the table does not exist. What is the name of your table and what are the fields in your table.

The line that is erroring on is Set rs = com.Execute

Thanks!

modest · Mar 9, 2005

You shouldn't copy the following text for text:
LDAP://directory.domain.com/aid=SubFolder,ou=Folder,o=domain.com,c=us

You have to use your networks own domain and containers, as well as identifiers (it's not necessarily aid, or o, or ou, or c,.. you see a lot of dc's and cn's in other people's LDAP setups). You have to contact your LDAP admin for this... or open an LDAP browser and explore.

modest · Jan 8, 2007

I forgot to mention that *xternal* was a search being performed with two wildcards much like any other SQL.

In regular SQL the Where clause would be something like:
WHERE userreferencedn Like "*xternal*"

*xternal* could be replaced with any other value being searched

associates · Mar 4, 2009

Hi Modest,

I was trying to retrieve all entries from my LDAP server through Access 03 and bumped into the thread you posted a while ago. I personally found your codes here are very helpful for me as it helps me find a way to connect to the machine where LDAP server resides.

Anyway, I run into runtime error with the following message "Table does not exist".

I modified some variables in your codes to suit my needs as described below

Code:

    ADsPath = "LDAP://10.1.1.100/aid=it0030,ou=addressbook,dc=somecompany,dc=com,dc=au"
  'set the connection
    Set con = New ADODB.Connection
    With con
        .Provider = "ADSDSOObject"
        '.Properties("User ID") = "cn=Directory Manager" ',aid=it0030,ou=Applications,o=domain.com,c=us"
        '.Properties("Password") = "password"
        .Open "ADSI"
    End With
    
  'set the command object
    Set com = New ADODB.Command
    With com
        .ActiveConnection = con
        .CommandText = "<" & ADsPath & ">;(userreferencedn=*);ADsPath;subtree"
    End With
     
    Set rs = com.Execute

Based on debugging, it stated that the error is in the line where "Set rs = com.Execute". Just wonder if you could give me some help with the error above.

Thank you in advance

mr.mike · Oct 3, 2013

I know this is an OLD post, but I'm trying to accomplish something similar. I can do it in VB.NET, but the company I'm working for wants an Access frontend. I can make my query pull back records, but when I try to take the recordset to populate a form I get a great big Access crash.

Here is a link to my proof of concept app...
skydrive.live.com/redir.aspx?cid=f92c43c82f100148&page=self&resid=F92C43C82F100148!203&parid=F92C43C82F100148!113&authkey=!AuWadKzmixLcXlI&Bpub=SDX.SkyDrive&Bsrc=Share

(I can't post links yet, so copy/paste the link above with https)

If someone knows how to get around the crash I would appreciate the assistance.

LDAP ADO Query (1 Viewer)

modest

Registered User.

modest

Registered User.

modest

Registered User.

ghudson

Registered User.

modest

Registered User.

modest

Registered User.

ghudson

Registered User.

modest

Registered User.

modest

Registered User.

associates

Registered User.

mr.mike

New member

Similar threads

Users who are viewing this thread