Solved

Help with ADO

Posted on 2002-04-04
13
570 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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

16 Experts available now in Live!

Get 1:1 Help Now