Recordset From ADSI Limited To 5000 Records

Please can someone kindly help me with this (VB code running within a SQL DTS)...
      
      Dim SQLConnectionObj, SQLConnectionStr
       Set SQLConnectionObj=CreateObject("ADODB.Connection")
       SQLConnectionStr="[connection string details here]"

       Dim MyDirectoryConnectionObj, MyDirectoryRecordSet
       Set MyDirectoryConnectionObj=CreateObject("ADODB.Connection")
      Set MyDirectoryRecordSet=CreateObject("ADODB.Recordset")

      SQLConnectionObj.Open SQLConnectionStr

      SQLConnectionObj.Execute "CREATE TABLE dbo.Lockdown_MyDirectoryFieldsFromDTS " & _
          "(" & _
          "UserID nvarchar(50), " & _
          "UserLastName nvarchar(255), " & _
          "UserFirstName nvarchar(255), " & _
          "ManagerID nvarchar(50), " & _
          ");"

      Dim FirstLetter, sBase, sFilter, sAttribs, sDepth, sQuery, ManagerString, MgrIDStart, MgrIDEnd
      Dim UserID, UserLastName, UserFirstName, ManagerID
      MyDirectoryConnectionObj.Open "DataSource=Active Directory Provider;Provider=ADsDSOObject"      

      For FirstLetter=65 To 90
            sBase="<LDAP://MyDirectoryDIR>"
            sFilter="(&(employeeType=ACTIVE)(sn=" & CHR(FirstLetter) & "*))"
            sAttribs="uid,sn,givenName,Manager"
            sDepth="subTree"
            sQuery=sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth
            Set MyDirectoryRecordSet=MyDirectoryConnectionObj.Execute(sQuery)
            
                  While Not MyDirectoryRecordSet.EOF

                        If ISNULL(MyDirectoryRecordSet(0)) Then
                              UserID="ID Not Found"
                        Else
                              UserID=Replace(MyDirectoryRecordSet(0),"'","''")
                        End If

                        If ISNULL(MyDirectoryRecordSet(1)) Then
                              UserLastName="Last Name Not Found"
                        Else
                              UserLastName=Replace(MyDirectoryRecordSet(1),"'","''")
                        End If

                        If ISNULL(MyDirectoryRecordSet(2)) Then
                              UserFirstName="First Name Not Found"
                        Else
                              UserFirstName=Replace(MyDirectoryRecordSet(2),"'","''")
                        End If

                        If ISNULL(MyDirectoryRecordSet(3)) then
                              ManagerID="ID Not Found"
                        Else
                              ManagerString=Replace(MyDirectoryRecordSet(3),"'","''")
                              MgrIDStart=Instr(ManagerString,"OU=")+3
                              MgrIDEnd=Instr(MgrIDStart,ManagerString,",")-1
                              ManagerID=Mid(ManagerString,MgrIDStart,(MgrIDEnd-MgrIDStart)+1)      
                        End If
                        
                        SQLConnectionObj.Execute "INSERT INTO Lockdown_MyDirectoryFieldsFromDTS VALUES (" & _
                        "'" & UserID & "'," & _
                        "'" & UserLastName &  "'," & _
                        "'" & UserFirstName &  "'," & _
                        "'" & ManagerID & "')"
            
                        MyDirectoryRecordSet.MoveNext

                  Wend

      Next

For some reason, each recordset appears to be limited to returning just 5000 records. I have played around with several parameters of the RecordSet object such as MaxRecords, PageSize, CacheSize however RecordCount always equals 5000.

Not sure where else to turn!!!  If not a RecordSet issue, could it be a limitation of ADSI, ODBC or something else?  Can't seem to find a similar issue elsewhere (with 1000 yes [for ADSI] but not 5000?).

Many thanks,
Tony
LVL 1
antdowAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
grayeConnect With a Mentor Commented:
It certainly looks like the PageSize is the way to go....
Dim Com As New Command

Com.Properties("Page Size") = 999
Com.Properties("Timeout") = 30     ' Seconds
Com.Properties("Cache Results") = False     ' Do not cache the result set.
http://msdn.microsoft.com/en-us/library/aa746459(VS.85).aspx
0
 
HoggZillaCommented:
There was a bug in MDAC that related to 5000 record limits. Check this link and make sure your MDAC is updated.
http://support.microsoft.com/kb/248668
 
0
 
HoggZillaCommented:
Here is the MSDN site for MDAC.
http://msdn.microsoft.com/en-us/data/aa937730.aspx
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
antdowAuthor Commented:
Thanks for the response but the symptoms don't appear to match my own.  I am not using the Oracle driver and I do not receive any error messages, just the recordcount does not go above 5000?
0
 
HoggZillaCommented:
I can tell you this, the ADODB Recordset does not have a limitation itself. The only limit is based on available system resources. I will keep looking for a solution.
0
 
antdowAuthor Commented:
Thank you Graye, it works!  I was setting Page Size on the RecordSet - which appeared to be valid - (as I didn't have a Command object since executing SQL directly from the Connection object).  Now that I have added an ADO Command object to the mix, I am the proud owner of a 12,000+ strong recordset. Thanks again for your help!
0
All Courses

From novice to tech pro — start learning today.