Connect Datasheet form to Active Directory Search Results (1 Viewer)

[ozinm]

[SOLVED] Connect Datasheet form to Active Directory Search Results

Hi All,
I'm trying to tie a from into the results from and AD Query.
I'm not that experienced with doing this kind of thing so I may be going about this the wrong way.
Anyway here's what I've got so far:

Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   Set cn = New ADODB.Connection

   With cn
    .ConnectionString = "Provider=ADSDSOObject;Trusted_Connection=yes;"
    .Open
   End With

   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT mail, cn From 'LDAP://DC=my,DC=domain,DC=com' WHERE memberOf='CN=MYGROUP,OU=Groups,OU=GroupOU,OU=MY,DC=domain,DC=com' AND objectCategory='user'"
      .Open
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub

When stepping through the code, it all goes well until it hits the line that actually connects it to the form "Set Me.Recordset = rs".
When this line is executed, Access crashes and attempts to restart.

Any ideas what I'm doing wrong?

PS: Access 2010, Win7 64bit

 

[Galaxiom]

Specify a client side cursor for the recordset.
.CursorLocation = adUseClient

This can be applied to either the Recordset or the Connection object.

 

[ozinm]

Specify a client side cursor for the recordset.
.CursorLocation = adUseClient

This can be applied to either the Recordset or the Connection object.

Thanks Galaxiom,
Certainly getting there. It's not crashing Access anymore and it's displaying the data in the first record and I can see there are 991 records returned.
However, it's also throwing the following error:

Run-time error '-2147467259 (80004005)': Data provider or other service returned an E_FAIL status.

and if I step through the rest of the records using the form record controls the fields are displaying "#Name?".

Any ideas what it could be.
I'm happy to do some digging around if you point me in the right direction - I just haven't got a clue about this stuff (yet!).

Thanks

Marc

 

[ozinm]

I've got an idea what it might be.
It looks like it only throws that error when there's around 1000 records or more to return.

 

[Galaxiom]

Maybe you can work around the issue with the MaxRecords Property.
http://msdn.microsoft.com/en-us/library/windows/desktop/ms675268(v=vs.85).aspx

 

[ozinm]

Sorted!

Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   Set cn = New ADODB.Connection

   With cn
    .ConnectionString = "Provider=ADSDSOObject;Trusted_Connection=yes;"
    .Open
   End With

   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Properties("page size") = 1000000
      .Source = "SELECT mail, cn From 'LDAP://DC=my,DC=domain,DC=com' WHERE memberOf='CN=MYGROUP,OU=Groups,OU=GroupOU,OU=MY,DC=domain,DC=com' AND objectCategory='user'"
      .Open
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub

Apparently there's a 1000 record limit by default with AD.
I added

.Properties("page size") = 1000000

and that fixed the issue.

Thanks Galaxiom for your help!