Running SQL Code in Access Query

[CharlieT302]

Hi All,

The code below (with help from some great people here)successfully builds a table by pulling Active Directory fields.

I would really love to be able to incorporate in into an Access query. I can then use the query as a data source for combo boxes, etc. Can this be done?

Also, to change the code to a Make Table equivalent, would I change the "Insert Into" line to a Select statement? And what would be the syntax?

How can I accomplish these two things?

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
DoCmd.RunSQL "INSERT INTO tbl_Sample_staff (givenname, Sn)VALUES (""" & Replace(NZ(objRecordset!givenname,""), """", """""") & _
""", """ & Replace(NZ(objRecordset!sn,""), """", """""") & """)"

While Not objRecordset.EOF

objRecordset.MoveNext
Wend


objConnection.Close

 

[lameid]

Your DoCmd.RunSQL statement should be in your while loop.

Because you are appending a record for each record in AD, you can not readily use a Maket Table... it would delete the table on each run leaving one record in your table. The syntax would be different. If you want to see it, I suggest you create a make table query in the query designer and switch to SQL view or look in help.

For your Select statement do you mean using the exising table?

Select  tbl_Sample_staff.*
From  tbl_Sample_staff

The best you could do with your current code is to create a table and then add values.

 

[CharlieT302]

Hi Lameid,

I am getting a little frustrated with this code. I am "almost" where I want to be. I appreciate your help.

Originally, all I wanted to do was to link to Active Directory and get a list of staff names. I want to use the list in combo boxes, queries, etc.

1.A query would be the easiest way to pull the names. I guess I need to know if my code can be incorporated into one. I am also getting many Null values currently. A query can filter these out easily.

2. A Select Into statement will re-write a table rather than adding records to it. This saves me from having to clear the table to prevent duplicate records.

3. Also, if you can help, I have another code that pulls Active Directory fields into a combo box. It works, but I cannot figure out the Where command to filter out Null values.

Combo Box Code:
Me.Combo2.AddItem (objRecordset.Fields("givenname") & "," & objRecordset.Fields("sn"))

Actually, I may need to apply a Where statement to each. I am not that familiar with SQL statement, so any help on these matters is "greatly" appreciated.

 

[lameid]

1. You can base a query off the table your code puts records in. You could also use an if statment to not insert when the values are null.

2. You are stuck here as I said before. You can't link directly to Active Directory with an Access query. You have to process each AD record. Repeatedly deleting tables and recreating them is generally a bad idea anyways. It leads to database bloat and increases the chances of corruption.

3. Use the same NZ function I helped you add to your SQL statement. Maybe combine with an if statement to not include the null ones. You might consider the isnull function with this option.

 

[CharlieT302]

Lameid

I tried the code below but received an error (I am not at the office now so can't retrieve error):

Me.Combo2.AddItem (Replace(Nz(objRecordset!givenname, ""), "'", "''") & "," & Replace(Nz(objRecordset!givenname, ""), "'", "''"))

Even still, I really need to be able attach a Where statement to the end to filter out any unwanted value. What would the proper syntax be?

 

[lameid]

I don't know why it errored but you don't need the replace in this situation.

I wrapped the code in an if statement so it won't add values with a null given name to the list.

If Not(isnull(objRecordset!givenname)) Then
     Me.Combo2.AddItem Nz(objRecordset!givenname, "") & "," & Nz(objRecordset!sn, "")
End If

 

[CharlieT302]

It may very well have been a typo on my part. I will check tomorrow. We have other values in the givenname field such as room names, etc.

This is why I have been trying to attach a Where statement but, up until now, haven't gotten the syntax correct. If I wanted let's say:
givenname is not training room 1

or to block all training rooms:
givenname is not training room * (asterisk ?)

 

[lameid]

If Not(isnull(objRecordset!givenname)) and instr(1,NZ(objRecordset!givenname, "training room") = 0 Then
     Me.Combo2.AddItem Nz(objRecordset!givenname, "") & "," & Nz(objRecordset!sn, "")
End If

 

[CharlieT302]

Thank you Lameid,

For future reference: Is this the only way of doing this. Is a Where statement possible? If so, how?

Also, my original question about the Select...Into statement. How could I alter my original code (above) to create a table instead of appending into one. My dept. really wants this ability. I haven't found good examples as of yet to help me.

 

[lameid]

You can only use a where in a query. From what I can tell, there is no way to start with a query but you can use a recordset so in this scenario, the if statement is the only way to go when loading the data.

Because you are adding the records one at a time there is no way to simply change your SQL statement to a make table. The Second record would delete the table when the first is already in it.

Before you begin your loop where you append the records, you could delete all the records in the table.

docmd.runsql "Delete tbl_Sample_staff.* From tbl_Sample_staff"