Recordset help
Breeze
Set adoRecordset = adoCommand.Execute
I am trying to loop though the recordset:
Do While Not adoRecordset.EOF
MsgBox "some text here"
adoRecordset.MoveNext
Loop
The messagebox was not appearing so I assumed the recorset was empty,
however when I added a recordcount. I found the recordset was not empty. So I
checked whether EOF was true: It was. I also checked EOF and it was also
true. My understanding is that if BOF and EOF are both true then the
recordset is empy. Hoow can the recordset be empy if I am getting a
recordcount in the 100s?
MsgBox adoRecordset.RecordCount - returned 1018
MsgBox "BOF = " & adoRecordset.BOF - returned true
MsgBox "EOF = " & adoRecordset.EOF - returned true
Do While Not adoRecordset.EOF
MsgBox "some text here"
adoRecordset.MoveNext
Loop
Klatuu
no reason to change, but I do know that in DAO it is a good practice to fully
populate the recordset before you start using it. See if any of this helps:
Set adoRecordset = adoCommand.Execute
With adoRecordset
If .RecordCount = 0 Then
MsgBox "No Records Found"
Else
.MoveLast
.MoveFirst
Do While Not .EOF
MsgBox "some text here"
.MoveNext
Loop
End If
End With
--
Dave Hargis, Microsoft Access MVP
Dirk Goldgar
news:390EFE34-FB9A-4C55...@microsoft.com...
>I am not terribly familiar with ADO, having used DAO for 10 years and
>seeing
> no reason to change, but I do know that in DAO it is a good practice to
> fully
> populate the recordset before you start using it. See if any of this
> helps:
>
> Set adoRecordset = adoCommand.Execute
> With adoRecordset
> If .RecordCount = 0 Then
> MsgBox "No Records Found"
> Else
> .MoveLast
> .MoveFirst
> Do While Not .EOF
> MsgBox "some text here"
> .MoveNext
> Loop
> End If
> End With
"[I]t is a good practice to fully populate the recordset before you start
using it."
What makes you say that, Dave? The only reason I know of for doing a
.MoveLast, .MoveFirst before processing a recordset is to make the
.RecordCount property accurate. If you don't need to know how many records
were returned before working with them, I can't think of any reason for
adding the extra overhead of forcing all records to be retrieved before
processing the first record.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
Dirk Goldgar
news:92260628-9C48-4178...@microsoft.com...
That's odd. If the RecordCount can't be determined yet, it's supposed to
have a value of -1, but I can't see why it would be 1018 if .EOF and .BOF
are both true. Would you mind posting the whole code from the point where
you create the Recordset object to the point where you exit the loop?
Brent Spaulding (datAdrenaline)
neccessary to even .MoveLast to have the .RecordCount return the number of
records in an ADO Recordset.
In DAO, I am with Dirk .. no need to populate (MoveLast) it, unless you want
the .RecordCount property to reflect the number of records contained in the
recordset.
The reason you have to fully populate a DAO recordset for the RecordCount to
reflect the number of records is that, by definition, in DAO the RecordCount
property is the number of records ACCESSED (and DAO populates, or accesses,
the records as they are needed). Where as in ADO, the RecordCount property
is defined as the number of records in the recordset.
--
Brent Spaulding | datAdrenaline | Access MVP
"Dirk Goldgar" <d...@NOdataSPAMgnostics.com.invalid> wrote in message
news:450C904A-53E7-4ADF...@microsoft.com...
Brent Spaulding (datAdrenaline)
--
Brent Spaulding | datAdrenaline | Access MVP
"Breeze" <Bre...@discussions.microsoft.com> wrote in message
news:92260628-9C48-4178...@microsoft.com...
Breeze
Private Sub Command0_Click()
Dim adoConnection As ADODB.Connection
Dim ADDatabase As Database
Dim adoRecordset As ADODB.Recordset
' Connect to the AD database and create a recordset of all the user accounts
Set ADDatabase = CurrentDb
' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection
' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"
' Filter on computer objects.
strFilter = "(objectCategory=user)"
' Comma delimited list of attribute values to retrieve.
strAttributes =
"objectGuid,whencreated,sAMAccountName,GivenName,sn,description,department,manager,ou,userAccountControl,accountExpires"
' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False
' Run the query.
Set adoRecordset = adoCommand.Execute
MsgBox adoRecordset.RecordCount
MsgBox "BOF = " & adoRecordset.BOF
MsgBox "EOF = " & adoRecordset.EOF
Do Until adoRecordset.EOF
MsgBox "Hello"
adoRecordset.MoveNext
Loop
MsgBox "done"
End Sub1
I did try the movelast and movefirst before I posted yesterday, but got an
error message in both cases as essentially the recordset is being reported as
emtpy. I am creating the recordset from AD could it possibly have something
to do with that?
Thanks for the help.
Peter
Klatuu
populates on opening.
As to DAO, I was told back a long time ago it improved overal performance
and that it was possible to get an erroneous EOF. I have never experienced
this, but only going on the advice of someone who had more experience than I
at that time.
--
Dave Hargis, Microsoft Access MVP
Breeze
Private Sub Command0_Click()
MsgBox "BOF = " & adoRecordset.BOF
MsgBox "EOF = " & adoRecordset.EOF
Jeff Burris
Thanks,
Jeff
> On Thursday, June 12, 2008 2:59 PM Breez wrote:
> I have created a recordset using the command:'
>
> Set adoRecordset = adoCommand.Execute
>
> I am trying to loop though the recordset:
>
> Do While Not adoRecordset.EOF
> MsgBox "some text here"
> adoRecordset.MoveNext
> Loop
>
> The messagebox was not appearing so I assumed the recorset was empty,
> however when I added a recordcount. I found the recordset was not empty. So I
> checked whether EOF was true: It was. I also checked EOF and it was also
> true. My understanding is that if BOF and EOF are both true then the
> recordset is empy. Hoow can the recordset be empy if I am getting a
> recordcount in the 100s?
>
> MsgBox adoRecordset.RecordCount - returned 1018
> Do While Not adoRecordset.EOF
> MsgBox "some text here"
> adoRecordset.MoveNext
> Loop
>> I am not terribly familiar with ADO, having used DAO for 10 years and seeing
>> no reason to change, but I do know that in DAO it is a good practice to fully
>> populate the recordset before you start using it. See if any of this helps:
>>
>> Set adoRecordset = adoCommand.Execute
>> With adoRecordset
>> If .RecordCount = 0 Then
>> MsgBox "No Records Found"
>> Else
>> .MoveLast
>> .MoveFirst
>> Do While Not .EOF
>> MsgBox "some text here"
>> .MoveNext
>> Loop
>> End If
>> End With
>> Dave Hargis, Microsoft Access MVP
>>
>>
>>> On Thursday, June 12, 2008 6:39 PM Dirk Goldgar wrote:
>>> "Klatuu" <Kla...@discussions.microsoft.com> wrote in message
>>> news:390EFE34-FB9A-4C55...@microsoft.com...
>>>
>>>
>>> using it."
>>>
>>> What makes you say that, Dave? The only reason I know of for doing a
>>> .MoveLast, .MoveFirst before processing a recordset is to make the
>>> .RecordCount property accurate. If you don't need to know how many records
>>> were returned before working with them, I can't think of any reason for
>>> adding the extra overhead of forcing all records to be retrieved before
>>> processing the first record.
>>>
>>> --
>>> Dirk Goldgar, MS Access MVP
>>> www.datagnostics.com
>>>
>>> (please reply to the newsgroup)
>>>> "Breeze" <Bre...@discussions.microsoft.com> wrote in message
>>>> news:92260628-9C48-4178...@microsoft.com...
>>>>
>>>>
>>>> have a value of -1, but I can't see why it would be 1018 if .EOF and .BOF
>>>> are both true. Would you mind posting the whole code from the point where
>>>> you create the Recordset object to the point where you exit the loop?
>>>>
>>>> Dirk Goldgar, MS Access MVP
>>>> www.datagnostics.com
>>>>
>>>> (please reply to the newsgroup)
>>>>> In ADO, the recordset is fully populated upon opening anyway, so it is not
>>>>> neccessary to even .MoveLast to have the .RecordCount return the number of
>>>>> records in an ADO Recordset.
>>>>>
>>>>> In DAO, I am with Dirk .. no need to populate (MoveLast) it, unless you want
>>>>> the .RecordCount property to reflect the number of records contained in the
>>>>> recordset.
>>>>>
>>>>> The reason you have to fully populate a DAO recordset for the RecordCount to
>>>>> reflect the number of records is that, by definition, in DAO the RecordCount
>>>>> property is the number of records ACCESSED (and DAO populates, or accesses,
>>>>> the records as they are needed). Where as in ADO, the RecordCount property
>>>>> is defined as the number of records in the recordset.
>>>>>
>>>>> --
>>>>> Brent Spaulding | datAdrenaline | Access MVP
>>>>>
>>>>> "Dirk Goldgar" <d...@NOdataSPAMgnostics.com.invalid> wrote in message
>>>>> news:450C904A-53E7-4ADF...@microsoft.com...
>>>>>> Anxiously awainting the real code ....
>>>>>>
>>>>>> Brent Spaulding | datAdrenaline | Access MVP
>>>>>>>> On Friday, June 13, 2008 9:51 AM Klatu wrote:
>>>>>>>> AS I first stated, I am not that familiar with ADO and not aware if fully
>>>>>>>> populates on opening.
>>>>>>>> As to DAO, I was told back a long time ago it improved overal performance
>>>>>>>> and that it was possible to get an erroneous EOF. I have never experienced
>>>>>>>> this, but only going on the advice of someone who had more experience than I
>>>>>>>> at that time.
>>>>>>>> --
>>>>>>>> Dave Hargis, Microsoft Access MVP
>>>>>>>>
>>>>>>>>
>>>>>>>> "Brent Spaulding (datAdrenaline)" wrote: