asked on

Help with ADO

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

mdougan

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.....

In the command text Add "TOP 10" for first 10 records only,
like this "SELECT TOP 2 * FROM table1"

pipe

ASKER

when i try to set this:

mrsPeople.Source = mcmdQuery

i get a type mismatch. i am using ado 2.7

any thoughts?

pipe

ASKER

i added...set ....testing to see if it works.

TravisHall

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.

mdougan

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.

TravisHall

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).

pipe

ASKER

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.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mdougan

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...

TravisHall

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.

mdougan

agreed

pipe

ASKER

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.