Any Ideas, AnyOne, Hello!

From: StephenMcC (anonymous_at_discussions.microsoft.com)
Date: 04/02/04 

Date: Fri, 2 Apr 2004 02:26:05 -0800

Re: Large RecordSet Problems/Erorrs with ADO & Active Directory

Hi all,

I'm having trouble querying a large record set using ADO and the LDAP sql dialect, creating an ADO connection, handing this to a command obj along with my SQL and executing this to create my recordset. This works fine when I point to a root in Active Directory which has a few hundred child objects, where as when I read the large location I get problems (holding on average 1.5 million enteries), the VB app. hangs or just returns a recordcount of 0 after a llllong wait! And all I really want to do is count all the items in a specified root, I've tried to get access to the RecordCount property, tried looping for each item and managing my own count, setting the timeout properties for the ado command/connection obj's incase this was an issue, even setting these to 0 to avoid timing-out, to no avail.

I took a look at using the ldap object to get directory information by getting an LDAP/AD object, binding to a root and gettin' the info for this, but again the sheer size is too much, anyways from what I read on the news groups ADO should be the best way to do this.

So wot I'm wondering is, can ADO mange such a large recordset, r there special tweaks I need to apply to make his possible. I really am at a loss and any help advice woulkd be most welcome.

I've added my code below:

Thanx in advance,

Stephen. 

---
    Dim oCon As New ADODB.Connection
    Dim oRS As New ADODB.Recordset
    Dim oCmd As New ADODB.Command
    Dim strSQL As String, strText As String
    
    strSQL = "SELECT cn FROM 'LDAP://server:port/o=root/ou=members/ou=test' WHERE objectClass='member' and cn='*'"
    '''strSQL = "<LDAP://server:port/o=root/ou=members/ou=test>;(&(cn=*));cn"
    
    Set oCon = CreateObject("ADODB.Connection")
    With oCon
        .Provider = "ADsDSOObject"
        .CommandTimeout = 0
        .ConnectionTimeout = 0
        .Open "ADs Provider", "cn=adm_unme, ou=members, o=root", "adm_pswrd"
    End With
    Set oCmd = CreateObject("ADODB.Command")
    Set oCmd.ActiveConnection = oCon
    With oCmd
        .CommandType = adCmdText
        .CommandText = strSQL
    '.CommandTimeout = 2000
        .Properties("Page Size") = 10000
    '.Properties("Asynchronous") = True
    '.Properties("Cache Results") = False
    '.Properties("Timeout") = 2000
    End With
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.CursorLocation = adUseClient
    Set oRS = oCmd.Execute()
    Dim i As Long
    Do Until oRS.EOF
        i = i + 1
        oRS.MoveNext
    Loop
    txtText.Text = "Record count: " & i
    
    Set oRS = Nothing
    Set oCmd = Nothing
    Set oCon = Nothing

Relevant Pages