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

x
?
Solved

How can I create a table from Active Directory using VBA and MS Access

Posted on 2014-09-10
4
Medium Priority
?
1,047 Views
Last Modified: 2014-09-11
The code below seems to run without error,  on a domain computer, but nothing happens.

I need a little help getting the data into a temporary table I can use for the needed feature.  I have button that runs this function, and I get a message on a non-domain computer that a domain could not be contacted, and nothing happens on the domain computer.

Basically I would like to access First Name, Last Name, username, email, etc. from AD and compare that to the same information in my app, to support importing new users to my app where employee information is also maintained.

The end result will be a button that will compare the app employee data with AD data, alert the user there are x number of new AD users, and/or x number of users no longer in AD and then facilitate the addition/removal.

All I need help with is making the AD connection and accessing the data there.  The app will also potentially be used by users who have multiple domain memberships.  Am I inherently limited to information of the domain the computer/user is currently logged into?

Thanks!
David

Public Function ActDirConnect()
On Error GoTo Err_ActDirConnect

   
 Dim adoConnection, strBase, strFilter, strAttributes
 Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strCN

 ' Setup ADO objects.
 Set adoConnection = CreateObject("ADODB.Connection")
 adoConnection.Provider = "ADsDSOObject"
 adoConnection.OPEN "Active Directory Provider"
 Set adoRecordset = CreateObject("ADODB.Recordset")
 Set adoRecordset.ActiveConnection = adoConnection

 ' Search entire Active Directory domain.
 Set objRootDSE = GetObject("LDAP://RootDSE")
 strDNSDomain = objRootDSE.Get("defaultNamingContext")
 strBase = "<LDAP://" & strDNSDomain & ">"

 ' Filter on user objects.
 strFilter = "(&(objectCategory=person)(objectClass=user))"

 ' Comma delimited list of attribute values to retrieve.
 strAttributes = "sAMAccountName,cn"

 ' Construct the LDAP syntax query.
 strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

 ' Run the query.
 adoRecordset.Source = strQuery
 adoRecordset.OPEN

 ' Enumerate the resulting recordset.
 Do Until adoRecordset.EOF
     ' Retrieve values and display.
     strName = adoRecordset.Fields("sAMAccountName").Value
     strCN = adoRecordset.Fields("cn").Value
     'Wscript.Echo "NT Name: " & strName & ", Common Name: " & strCN
     ' Move to the next record in the recordset.
     adoRecordset.MoveNext
 Loop

 ' Clean up.
 adoRecordset.Close
 adoConnection.Close
    
    
    
    
    
Exit_ActDirConnect:
    Exit Function

Err_ActDirConnect:
    MsgBox Err.Description & " - ActDirConnect error"
   
    Resume Exit_ActDirConnect
End Function

Open in new window

0
Comment
Question by:David Smithstein
[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
  • 2
4 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40315967
An a non domain computer it can NOT work as it tries to talk to the domain....

On a domain computer it works perfectly. It connects, queries, gets data and loops through records. Thing is you have not asked it to do anything with the data.

Run through the code in debug mode pressing F8 on each line and watch...
0
 

Author Comment

by:David Smithstein
ID: 40316031
The message on the non domain computer was expected, the part I need help with is how to ask it to do something with the data, like create or update a table in MS Access.

I'm also not sure how to ask for the specific fields available in active directory.  What code am I missing to do that?

Does adoRecordset.OPEN give me access to the AD Data?  Or should I make an access query that populates my local table within the Do Loop?  Does anyone know a good reference for AD fields besides sAMAccountName and cn referenced in this code so I know how to ask for the fields I want?

Thanks,
David
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40316762
Opening a recordset would give you access to whatever fields are included in the Recordset, so I'm not sure what you're asking in regard to that. You could open a Recordset and loop through it, and add the values from each record into an Access table, if that's what you're asking.

Regarding fields in the Active Directory tables, here's the starting point for the AD Schema: http://msdn.microsoft.com/en-us/library/ms675085%28VS.85%29.aspx

However, if you just want the fields available in that recordset, you can loop through the Fields collection:

Dim i As Integer
For i = 0 to YourRecordset.Fields.Count - 1
  Debug.Print YourRecordset.Fields(i).Name
Next i

That would print out a list of the field to the VBA Debug window.
0
 

Author Closing Comment

by:David Smithstein
ID: 40317505
Thanks!  I was just unsure if that code was supposed to produce a result I couldn't see.  I added a local table and few queries, one to clean it out, the other to append the user field as it cycled through obtaining the cn(user) data, and a third to display the results.  - Worked perfectly.  Now I just need the client to tell me what AD info we need to be working with based on your link, and we are all set. - Thanks again!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This process allows computer passwords to be managed and secured without using LAPS. This is an improvement on an existing process, enhanced to store password encrypted, instead of clear-text files within SQL
A bad practice commonly found during an account life cycle is to set its password to an initial, insecure password. The Password Reset Tool was developed to make the password reset process easier and more secure.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

722 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