20.2 Other Ways of Connecting and Retrieving Results

As mentioned earlier, there are a number of ways of authenticating an ADO connection to Active Directory. The simplest is the way outlined earlier using the Connection::Provider set with the username and password as second and third arguments:

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"
objConn.Open "", _
             "CN=Administrator,CN=Users,dc=mycorp,dc=com", _ 
             "mypass"

Because ADO is designed for databases, it is often necessary to specify a number of other requirements when opening a connection. These include a different provider, a different server, or a specific database. All of these items can be set prior to opening the connection. However, none of these make a difference to the AD provider. If you wish to open a connection by setting these values in the ConnectionString property, then do so as shown in the following code:

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"objConn.ConnectionString = _
  "DSN=;UID=CN=Administrator,CN=Users,dc=mycorp,dc=com;PWD=mypass"
objConn.Open

Semicolons separate the arguments, with the expected DataSourceName (DSN) specified as empty at the start of the string.

One important point: do not authenticate using both methods with the same connection—use one or the other. The following code uses both methods to illustrate what not to do:

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"
objConn.Open _
  "DSN=;UID=CN=Administrator,CN=Users,dc=mycorp,dc=com;PWD=mypass", _
  "CN=Administrator,CN=Users,dc=mycorp,dc=com", "mypass"

This is a slightly different version, but still wrong:

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"
objConn.ConnectionString = _
  "DSN=;UID=CN=Administrator,CN=Users,dc=mycorp,dc=com;PWD=mypass"
objConn.Open "", "CN=Administrator,CN=Users,dc=mycorp,dc=com", _
  "mypass"

20.2.1 Searching With SQL

You can retrieve resultsets in a variety of ways and get exactly the same values. We will now discuss how to use the Command object and the Recordset::Open method, using SQL-formatted queries to retrieve resultsets. SQL is a powerful query language that is the de facto standard to query database tables. We do not propose to go through the details of SQL here, but we will cover some examples for those who may already be familiar with SQL and would find using it to be a more comfortable way of querying Active Directory than using LDAP search filters.

20.2.1.1 Using the Connection::Execute method

You can pass a SQL select statement to a connection using the Execute method as we've done previously with LDAP-based queries:

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"
objConn.Open "", "CN=Administrator,CN=Users,dc=mycorp,dc=com", ""
   
Set objRS = objConn.Execute "Select Name, ADsPath" _
  & " FROM 'LDAP://dc=mycorp,dc=com' where objectclass = 'user'"

20.2.1.2 Using the Recordset::Open method

Next we will set the Recordset::ActiveConnection and Recordset::Source properties before the Recordset::Open method is called; the second passes values directly to the Recordset::Open method.

Setting the properties first:

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"
objConn.Open "", "CN=Administrator,CN=Users,dc=mycorp,dc=com", ""

'Open a recordset based on a SQL string by presetting the properties
   
Set objRS = CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn
objRS.Source = "Select Name, ADsPath FROM " _
  & "'LDAP://dc=mycorp,dc=com' where objectclass = 'user'"
objRS.Open

Passing values directly:

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"
objConn.Open "", "CN=Administrator,CN=Users,dc=mycorp,dc=com", ""
   
'Open a recordset based on a SQL string
   
'Set the SQL search string
strSource = "Select Name, ADsPath FROM 'LDAP://dc=mycorp,dc=com' " _
  & "where objectclass = 'user'"
   
'Pass what will become the Source and ActiveConnection properties to 
'the Recordset::Open call
objRS.Open strSource, objConn

20.2.1.3 Executing a specific command

You can use the Command object's methods and properties to pass a complete command to an already open connection:

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"
objConn.Open "", "CN=Administrator,CN=Users,dc=mycorp,dc=com", ""
   
'Opening a recordset based on a command object
   
Set objComm = CreateObject("ADODB.Command")Set objComm.ActiveConnection = objConn
objComm.CommandText = "Select Name, ADsPath FROM" _
  & " 'LDAP://dc=mycorp,dc=com' where objectclass = 'user'"
   
Set objRS = objComm.Execute(  )

Or you can do this:

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"
objConn.Open "", "CN=Administrator,CN=Users,dc=mycorp,dc=com", ""
   
'Opening a recordset based on a Command object
   
Set objComm = CreateObject("ADODB.Command")
Set objComm.ActiveConnection = objConn
   
Set objRS = objComm.Execute "Select Name, ADsPath FROM" _
  & " 'LDAP://dc=mycorp,dc=com' where objectclass = 'user'"

Controlling How a Search Is Conducted When conducting a search, the Command object can take a number of extra parameters. For example, a parameter can dictate how many results are returned (Page Size), how long in seconds the command can take before it fails (Timeout), how far to search in the database (Searchscope), and whether the resultset is cached in memory for faster access at a later date (Cache Results). These four values are shown in the following code section: Const ADS_SCOPE_SUBTREE = 2 Set objComm = CreateObject("ADODB.Command") objComm.Properties("Page Size") = 10000 objComm.Properties("Timeout") = 60 objComm.Properties("Searchscope") = ADS_SCOPE_SUBTREE objComm.Properties("Cache Results") = False For more information, consult the ADO Command object in MSDN.

20.2.1.4 The Command object and Recordset::Open

You can even combine the Command object and Recordset::Open, like this:

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"
objConn.Open "", "CN=Administrator,CN=Users,dc=mycorp,dc=com", ""
   
Set objComm = CreateObject("ADODB.Command")
Set objComm.ActiveConnection = objConn
objComm.CommandText = ""Select Name, ADsPath FROM" _
  & " 'LDAP://dc=mycorp,dc=com' where objectclass = 'user'"
   
'Pass what will become the Source and ActiveConnection properties to 
'the Recordset::Open call
objRS.Open objComm, objConn