Append Active directory information to an access table

[pnvekaria]

Hi All,

I am try to adapt the VBS code below to search for users in an active directory and append an exsisting access 2007 table.

The code as is works but the output is in excel, but I would like to use it with access.

Can someone help with adapting the vbs code into access VBA module and get it to display the results on the screen for selection before appending the required user data into an exsisting table.

Regards,

Pank

Below is the VBS code.

' Bind to RootDSE - this object is used to
' get the default configuration naming context
' e.g. dc=microsoft,dc=co,dc=uk
set objRootDSE = getobject("ldap://RootDSE")

' Search box to search for user name
Name = InputBox("Enter Surname Name to search?")
' File name to export to
strExportFile = "C:user.xlsx"
' Root of search set to default naming context.
' e.g. dc=microsoft,dc=co,dc=uk
' RootDSE saves hard-coding the domain.
' If want to search within an OU rather than the domain,
' specify the distinguished name of the ou. e.g.
' ou=students,dc=microsoft,dc=co,dc=uk"
strRoot = objRootDSE.Get("DefaultNamingContext")
' Filter for user accounts - could be modified to search for specific users,
' such as those with mailboxes, users in a certain department etc.
strfilter = "(&(objectCategory=Person)(objectClass=User)(sn="&Name&"))"
' Attributes to return from the query
strAttributes = "sAMAccountName,givenName,sn," & _
"physicalDeliveryOfficeName," & _
"mail," & _
"title,department," & _
"company," & _
"l"

'Scope of the search. Change to "onelevel" if you didn't want to search child OU's
strScope = "subtree"
set cn = createobject("ADODB.Connection")
set cmd = createobject("ADODB.Command")
cn.open "Provider=ADsDSOObject;"
cmd.ActiveConnection = cn
cmd.commandtext = "<LDAP://" & strRoot & ">;" & strFilter & ";" & _
strAttributes & ";" & strScope
set rs = cmd.execute
' Use Excel COM automation to open Excel and create an excel workbook
set objExcel = CreateObject("Excel.Application")
set objWB = objExcel.Workbooks.Add
set objSheet = objWB.Worksheets(1)
' Copy Field names to header row of worksheet
For i = 0 To rs.Fields.Count - 1
objSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
objSheet.Cells(1, i + 1).Font.Bold = True
Next
' Copy data to the spreadsheet
objSheet.Range("A2").CopyFromRecordset(rs)
' Save the workbook
objWB.SaveAs(strExportFile)
' Clean up
rs.close
cn.close
set objSheet = Nothing
set objWB = Nothing
objExcel.Quit()
set objExcel = Nothing

 

[DavidAtWork]

The code above creates and then opens a connection to the data source, then creates a new workbook and dumps the recordset on a worksheet, all you need to do is create a connection to a database and use SQL to write it (the recordset coming from rs) to a table in the database.
"and get it to display the results on the screen for selection before appending the required user data into an exsisting table"
This I don't know how you're going to solve
David

 

[Galaxiom]

To show the recordset on the screen simply display it in a form by setting the form's Recordset property to the ADO recordset.

To approve the records is a little trickier. For this, create another fabricated ADO Recordset which will become the recordset for the form.

Design the form with the fields you want to display and add a checkbox or toggle button to become the select element.

In the OnOpen Event of the display form:

Create a new blank ADO recordset without a Connection property.
Append the fields you want display to its Fields collection.
Append an extra Boolean field.

Open the recordset with the following properties:
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic

Loop through the data recordset and on each loop add a new record and copy the values of each field from the data recordset to the fabricated recordset.

Assign the Boolean field a True or False value depending on what you want as the default for the selection element. This field becomes the ControlSource for the selection element on the form.

Set the recordset as the form's recordset. Voila.

Change the state of the toggle button or checkbox on each record as you wish.

When you are happy with the selctions loop through the form's recordset and write the values to the destination table depending on the Boolean field.

 

[pnvekaria]

Thanks Guys,

I'll give it a try. I am not a programmer though but understand a little bit of VBA.

I'll give it a go and post the outcomes, if any.

Pank