Populating combo box and table with Active Directory fields

[CharlieT302]

Hi All,

The code below is capable of populating my combo box with the first and last name of staff from Active Directory (thanks to ZmrAbdulla). However, null values from Active directory fields are harming the results.

1. Filter out empty (null) values in the Givenname and SN fields.

2. I would like the option of inserting the results into a separate table rather than a combo box.

How can I accomplish these two items.

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

objConnection.Open "Provider=ADsDSOObject;"
objCommand.ActiveConnection = objConnection

objCommand.CommandText = _
"<LDAP://dc=ad,dc=Oggwin,dc=org>;(objectcategory=user);sn,givenname;subtree"

Set objRecordset = objCommand.Execute
Me.ComboBox1.RowSourceType = "Value List"

While Not objRecordset.EOF
Me.ComboBox1.AddItem (objRecordset.Fields("givenname") & "," & objRecordset.Fields("sn"))

objRecordset.MoveNext
Wend

objConnection.Close

I truly appreciate any help.

 

[lameid]

Generically speaking you want to make another recordest that opens the table you want to add records too.

Then you would add a record to the recordset, set the fields and update the record. Do this instead of adding a value to the combo box.

 

[CharlieT302]

Here is my latest attempt to place Active Directory records into a table using: DoCmd.RunSQL (see below)

The command will open the table, but it inserts a blank record and displays a parameter box that reads: Givenname.Value (the same for sn).

It apparantly is not recognizing the field names.

Any ideas?

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

objConnection.Open "Provider=ADsDSOObject;"
objCommand.ActiveConnection = objConnection

objCommand.CommandText = _
"<LDAP://dc=ad,dc=Oggwin,dc=org>;(objectcategory=user);sn,givenname;subtree"

Set objRecordset = objCommand.Execute
DoCmd.RunSQL ("INSERT INTO tbl_Sample_staff (givenname, Sn)VALUES (givenname.value,sn.value)")

While Not objRecordset.EOF

objRecordset.MoveNext
Wend


objConnection.Close

 

[lameid]

DoCmd.RunSQL ("INSERT INTO tbl_Sample_staff (givenname, Sn)VALUES (" & _ 
objRecordset!givenname & ", " & objRecordset!sn & ")")

 

[CharlieT302]

Thank you lameid,

However, after trying the code, it is displaying the error:

Syntax error in Insert Into statement

 

[lameid]

One or more of those fields is probably a text field and you need to embed single or double quotes in the string for the SQL to be right.

 

[CharlieT302]

Actually they both are text fields from Active Directory. I made a correction as you suggested.

The new code is:

DoCmd.RunSQL ("INSERT INTO tbl_Sample_staff (givenname, Sn)VALUES ('" & objRecordset!givenname & "', '" & objRecordset!sn & "')")

This new code does import records into the table, but it displays an error on one record:

"Syntax error (missing operator) in query expression "O'Neil")

Is it the apostrophe in "O'Neil" that is throwing it off?

 

[lameid]

Yes. You can use either single quotes or double quotes. However, you have to double up either if you want to represent the value in a string. Whether than changing your literal, I would modify your SQL to use double quotes because that is not likely to appear in your data...

DoCmd.RunSQL ("INSERT INTO tbl_Sample_staff (givenname, Sn)VALUES (""" & objRecordset!givenname & _
""", """ & objRecordset!sn & """)")

 

[PHV]

DoCmd.RunSQL "INSERT INTO tbl_Sample_staff (givenname,Sn)VALUES ('" & Replace(objRecordset!givenname, "'", "''") & "','" & Replace(objRecordset!sn, "'", "''") & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

PHV is right to remove the parenthesis around the SQL. The replace is another way to go. It is an absolute fix rather than assuming double quotes don't exist in the data like I did. You could of course add the replace in my code for double quotes. Stylistically I always use double quotes for all strings in VBA and SQL. It is easier for me to count double quotes than to discern whether it is a pair of sinlge quotes or a double quote. PHV likes the less clutter that mixing the two types yields. Take your pick there.

 

[CharlieT302]

Hey Guys,

I have tried it both ways with the following errors:

Lameid's Technique:
Number of query values and destination fields are not the same.

PHV's Techniques:
Invalid Use of Null

Any Ideas?

 

[lameid]

Hmmm... PHV's code is easier to fix at a glance...

DoCmd.RunSQL "INSERT INTO tbl_Sample_staff (givenname,Sn)VALUES ('" & Replace(NZ(objRecordset!givenname,""), "'", "''") & "','" & Replace(NZ(objRecordset!sn,""), "'", "''") & "')"

 

[lameid]

Aside from the parenthesis that I mentioned removing it looks good to me. I went ahead and updated it to show replacing the double quotes in strings.

DoCmd.RunSQL "INSERT INTO tbl_Sample_staff (givenname, Sn)VALUES (""" & Replace(NZ(objRecordset!givenname,""), """", """""") & _
""", """ & Replace(NZ(objRecordset!sn,""), """", """""") & """)"

 

[CharlieT302]

Great! That appeared to work. This deserves a star for both of you.

Question, was the Invalid Use of Null error caused because of Null values within the Active Directory fields?

 

[lameid]

Yes it was nulls in AD. The NZ function says use the first value if it is null otherwise use the second. Replace can't handle a null so I used NZ to pass zero length strings. It still looks like a blank but it is different.

 

[CharlieT302]

Hi,

I have been reflecting upon this code (I haven't been able to get back to the project in a few days). The code works fine. However, I have a couple of issues that should resolve this completely:

1. The final code (with help from PHV and Lameid - thank both of you) is basically an Append query. I am assuming to make it operate more like a Make Table query, I would alter the "Insert Into" statement with a Select statement?

2. Can this code be modified to run within an Access query; let's says by placing it into the SQL view of the query?

3. We may still opt use this code with a combo box. I have code that works, but could not get it to filter out Null values (see below):

Me.ComboBox1.AddItem (objRecordset.Fields("givenname") & "," & objRecordset.Fields("sn"))