Solved

Help with ADO

Posted on 2002-04-04
13
571 Views
Last Modified: 2013-11-23
hello-

i am using ado to pull info from ldap. i am trying to find a way to limit the number of records that are returned. so far, i havent had any luck. he is a snippet of my code.

thanks - p


    '' Consts
    Const METHOD = "DPhoneBookManager.Find"
    Const CDATA_OPEN = "<![CDATA["
    Const CDATA_CLOSE = "]]>"
   
    '' Vars
    Dim mcnnConnection As ADODB.Connection
    Dim mrsPeople As ADODB.Recordset
    Dim mcmdQuery As ADODB.Command
    Dim msPeople As String

    Set mcnnConnection = CreateObject("ADODB.Connection")
    Set mrsPeople = CreateObject("ADODB.Recordset")
    Set mcmdQuery = CreateObject("ADODB.Command")
   
    '' Set the connection properties.
    With mcnnConnection
        .Provider = "ADsDSOObject"
        .Properties("User ID") = ""
        .Properties("Password") = ""
        .Properties("Encrypt Password") = False
        .Open "ADs Provider"
    End With
       
    '' Set the command properties.
    With mcmdQuery
        mcmdQuery.ActiveConnection = mcnnConnection
        mcmdQuery.CommandText = vsSearchFilter
        mcmdQuery.Properties("Page Size") = 99
    End With
   
    Dim z As Integer
    For z = 0 To mcmdQuery.Properties.Count - 1
        Debug.Print mcmdQuery.Properties.Item(z).Name & " = " & mcmdQuery.Properties.Item(z).Value
    Next z
   
   
    Set mrsPeople = mcmdQuery.Execute

    Debug.Print "Record count " & mrsPeople.RecordCount
0
Comment
Question by:pipe
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 18

Expert Comment

by:mdougan
ID: 6919179
You could try setting the CacheSize on the recordset:

'' Set the command properties.
   With mcmdQuery
       mcmdQuery.ActiveConnection = mcnnConnection
       mcmdQuery.CommandText = vsSearchFilter
       mcmdQuery.Properties("Page Size") = 99
   End With
   
   Set mrsPeople = New ADODB.Recordset
   mrsPeople.Source = mcmdQuery
   mrsPeople.CacheSize = 100
   mrsPeople.Open

   Set mrsPeople.ActiveConnection = Nothing
   i = 1
   while not mrsPeople.EOF
        Debug.Print i & " " & mrsPeople.Fields(0).value
        i = i + 1
   Wend

You should print 100 records.....
0
 
LVL 1

Expert Comment

by:S2
ID: 6919265
In the command text Add "TOP 10" for first 10 records only,
like this "SELECT TOP 2 * FROM table1"
0
 

Author Comment

by:pipe
ID: 6919393
when i try to set this:

mrsPeople.Source = mcmdQuery


i get a type mismatch. i am using ado 2.7

any thoughts?
0
 

Author Comment

by:pipe
ID: 6919406
i added...set ....testing to see if it works.
0
 
LVL 2

Expert Comment

by:TravisHall
ID: 6919464
CacheSize won't help you here. It has nothing to do with the number of records the query returns, only with how many are sent to the client each time the client runs out of records. If you set CacheSize to 10, you will get 10 records to start with, then when you move to the eleventh record 10 more records will be retrieved, and so on and so forth.

S2's suggestion of using TOP in your SQL query should do the trick. TOP is generally combined with an ORDER BY clause, so that you have some idea which records will be returned.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6919602
Sorry, I think I forgot a Set statement:

Set mrsPeople.Source = mcmdQuery

TravisHall, you'll note in the code above that I'm setting the activeconnection to nothing after opening the recordset.  The thinking is that the first 100 records will be cached and returned in the recordset, then closing the connection would keep the rest from being retrieved.  It was just a guess, don't know if it will work, but I'll give it a shot.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Expert Comment

by:TravisHall
ID: 6919678
Setting ActiveConnection to Nothing gives you a disconnected recordset - but I don't believe you can produce a disconnected recordset unless you are using a client-side cursor (CursorLocation property set to adUseClient - otherwise, you'll get an error). And if you are using a client-side cursor, you will retrieve all the records, regardless of CacheSize (though, you can probably do that retrieval in lots of 100, not that it will make any difference).

If you don't like TOP, you could try setting the MaxRecords property of the recordset (which is intended to do exactly what was requested, I believe).
0
 

Author Comment

by:pipe
ID: 6919825
welp, i am pulling the records using LDAP. unfortunately i cannot find something similar to TOP in that protocal. i was hoping i could remedy that by setting some properties on the command or recordset objects but it doesnt seem to work.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 85 total points
ID: 6919850
mdougan is on the right track.

Since this is LDAP the SQL TOP keyword is not an option. In addition MaxRecords is not supported by the LDAP Provider.  Take a look at this article from MSDN:
HOWTO: Work Around the Limitation that ADO MaxRecord Is Not Implemented in DS OLEDB Provider ADsDSOObject (Q269361)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269361

Anthony
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6919920
Well, it's probably somewhere in the middle.  Travis has a point about the client-side cursor.  You're going to be running the entire query, but, if the CacheSize is set to 100, and as Anthony's article says, set the AbsolutePage to 1, then you'll have the first page of data.  Then, I guess that you have to close the RS after you finish processing the required number.

I guess it depends on what pipe wants to do, maybe it's good to leave the RS open, and just retrieve 100 at a time...
0
 
LVL 2

Expert Comment

by:TravisHall
ID: 6920025
With a client-side cursor, I would think that the entire recordset would be retrieved and stored in the client's cursor, but with the Filter property set correctly (to adFilterFetchedRecords), it seems that you will only get the current page's records regardless. So, by setting PageSize and CacheSize correctly, you build a cache containing a subset of the required records, and restrict yourself to only using them with the Filter. You don't need to worry about having too many records, because Filter is giving you just what you want.

However, I suspect that you will still end up with ADO retrieving all the data in the recordset, so if you are hoping to improve performance by only retrieving a subset of the data, it won't work. I guess it depends what you want this for.

The other downside, of course, is that you have used Filter to restrict the number of records you work with, which means that you can't pull off any of the other neat tricks you can normally do with the Filter property. As soon as you change Filter to something else, you are back to checking against the entire recordset.

On the other hand, I could be completely incorrect in my understanding of how the entire thing works.

Anyway, I'd say Anthony has given the answer most likely to work, or at least provide useful information.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6920923
agreed
0
 

Author Comment

by:pipe
ID: 6920990
wow, good job finding that article. this seems to work sometimes. im seeing some flakey errors but hopefully i can work them out. thanks again.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copy a row 12 57
creating threads in delphi 1 80
Search combo error "Data Type Mismatch in Criteria Expression" 2 49
Firemonkey android show image from resource ? 1 30
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now