ADO episode : ADO DAO RDO RDS

Using Office 2003 and Windows XP;

I have set up this sort of thing before without issue and now I'm clueless
(I must be losing it) as to why the following function in a standard code
module generates an error (item cannot be found in the collection
corresponding to the requested name or ordinal). Can someone please remind me
what I'm missing - [and thanks much in advance] ?

Private Function TestRecordset()

Dim sSQL As String
Dim rs As ADODB.Recordset
Dim sResult As String
Dim iCols As Integer
Dim iX As Integer

sSQL = "SELECT Table1.* FROM Table1;"
Set rs = CurrentProject.Connection.Execute(sSQL)

iCols = rs.Fields.Count - 1

rs.MoveFirst
Do
For iX = 0 To iCols + 1
sResult = sResult & ";" & rs!Fields(iX).Value & vbCr
Next iX
rs.MoveNext
Loop Until rs.EOF

MsgBox sResult

End Function

Also, please note that the SQL works fine in a query...

Remove the + 1 and replace the ! with the . :

For iX = 0 To iCols
sResult = sResult & ";" & rs.Fields(iX).Value & vbCr

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"XP" <XP@discussions.microsoft.com> wrote in message
news:436E14C1-DC44-4D2B-901D-6EE4854D96C6@microsoft.com...
> Using Office 2003 and Windows XP;
>
> I have set up this sort of thing before without issue and now I'm clueless
> (I must be losing it) as to why the following function in a standard code
> module generates an error (item cannot be found in the collection
> corresponding to the requested name or ordinal). Can someone please remind
> me
> what I'm missing - [and thanks much in advance] ?
>
> Private Function TestRecordset()
>
> Dim sSQL As String
> Dim rs As ADODB.Recordset
> Dim sResult As String
> Dim iCols As Integer
> Dim iX As Integer
>
> sSQL = "SELECT Table1.* FROM Table1;"
> Set rs = CurrentProject.Connection.Execute(sSQL)
>
> iCols = rs.Fields.Count - 1
>
> rs.MoveFirst
> Do
> For iX = 0 To iCols + 1
> sResult = sResult & ";" & rs!Fields(iX).Value & vbCr
> Next iX
> rs.MoveNext
> Loop Until rs.EOF
>
> MsgBox sResult
>
> End Function
>
> Also, please note that the SQL works fine in a query...

Thanks SL! I was going crazy...

Also, I had forgotten that "rs.RecordCount" doesn't work right, always
returns -1.

Do you happen to have a function you could post that will tell whether a
recordset is empty?

Thanks again.

"Sylvain Lafontaine" wrote:

> Remove the + 1 and replace the ! with the . :
>
> For iX = 0 To iCols
> sResult = sResult & ";" & rs.Fields(iX).Value & vbCr
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "XP" <XP@discussions.microsoft.com> wrote in message
> news:436E14C1-DC44-4D2B-901D-6EE4854D96C6@microsoft.com...
> > Using Office 2003 and Windows XP;
> >
> > I have set up this sort of thing before without issue and now I'm clueless
> > (I must be losing it) as to why the following function in a standard code
> > module generates an error (item cannot be found in the collection
> > corresponding to the requested name or ordinal). Can someone please remind
> > me
> > what I'm missing - [and thanks much in advance] ?
> >
> > Private Function TestRecordset()
> >
> > Dim sSQL As String
> > Dim rs As ADODB.Recordset
> > Dim sResult As String
> > Dim iCols As Integer
> > Dim iX As Integer
> >
> > sSQL = "SELECT Table1.* FROM Table1;"
> > Set rs = CurrentProject.Connection.Execute(sSQL)
> >
> > iCols = rs.Fields.Count - 1
> >
> > rs.MoveFirst
> > Do
> > For iX = 0 To iCols + 1
> > sResult = sResult & ";" & rs!Fields(iX).Value & vbCr
> > Next iX
> > rs.MoveNext
> > Loop Until rs.EOF
> >
> > MsgBox sResult
> >
> > End Function
> >
> > Also, please note that the SQL works fine in a query...
>
>
>

The rs.RecordCount won't work with some type of recordset but will work with
others. Don't remember what type of recordset
CurrentProject.Connection.Execute(sSQL) is returning, so you might want to
create your own recordset and open it using CurrentProject.Connection as the
connection object instead of calling .Execute.

For those type of recordsets where rs.RecordCount won't work, you can try to
call .MoveLast in order to populate this value. Don't remember all the
combinations; so you will have to make some testing by yourself.

Finally, I don't understand what you mean with an "empty recordset" but
probably you mean a recordset with no record; this can be tested with rs.EOF
when the recordset is first opened or after a call to .MoveFirst.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"XP" <XP@discussions.microsoft.com> wrote in message
news:A5D8DED3-A151-4451-A24E-351ABC092EAF@microsoft.com...
>
> Thanks SL! I was going crazy...
>
> Also, I had forgotten that "rs.RecordCount" doesn't work right, always
> returns -1.
>
> Do you happen to have a function you could post that will tell whether a
> recordset is empty?
>
> Thanks again.
>
> "Sylvain Lafontaine" wrote:
>
>> Remove the + 1 and replace the ! with the . :
>>
>> For iX = 0 To iCols
>> sResult = sResult & ";" & rs.Fields(iX).Value & vbCr
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "XP" <XP@discussions.microsoft.com> wrote in message
>> news:436E14C1-DC44-4D2B-901D-6EE4854D96C6@microsoft.com...
>> > Using Office 2003 and Windows XP;
>> >
>> > I have set up this sort of thing before without issue and now I'm
>> > clueless
>> > (I must be losing it) as to why the following function in a standard
>> > code
>> > module generates an error (item cannot be found in the collection
>> > corresponding to the requested name or ordinal). Can someone please
>> > remind
>> > me
>> > what I'm missing - [and thanks much in advance] ?
>> >
>> > Private Function TestRecordset()
>> >
>> > Dim sSQL As String
>> > Dim rs As ADODB.Recordset
>> > Dim sResult As String
>> > Dim iCols As Integer
>> > Dim iX As Integer
>> >
>> > sSQL = "SELECT Table1.* FROM Table1;"
>> > Set rs = CurrentProject.Connection.Execute(sSQL)
>> >
>> > iCols = rs.Fields.Count - 1
>> >
>> > rs.MoveFirst
>> > Do
>> > For iX = 0 To iCols + 1
>> > sResult = sResult & ";" & rs!Fields(iX).Value & vbCr
>> > Next iX
>> > rs.MoveNext
>> > Loop Until rs.EOF
>> >
>> > MsgBox sResult
>> >
>> > End Function
>> >
>> > Also, please note that the SQL works fine in a query...
>>
>>
>>

On Oct 3, 9:56 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> For those type of recordsets where rs.RecordCount won't work, you can try to
> call .MoveLast in order to populate this value.

I think you are thinking of a DAO trick: with ADO, navigating MoveLast
will not change the RecordCount property value. In fact, using a
server-side forward only cursor with a read only lock type with Jet,
the RecordCount will be -1 and attempting to MoveLast will generate a
run-time error.

Jamie.

--

Oh, I've seen reference about using MoveLast with ADO in order to set the
value of RecordCount, see for example
http://forums.devx.com/archive/index.php/t-46750.html .

However, you're right on the fact that the backend used (JET or SQL-Server)
as well as the location of the cursor (adUseClient or adUseServer) have in
influence on the result.

To the OP, here some more info:
http://support.microsoft.com/kb/194973

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Jamie Collins" <jamieuka@googlemail.com> wrote in message
news:1191579860.857306.243110@57g2000hsv.googlegroups.com...
> On Oct 3, 9:56 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>> For those type of recordsets where rs.RecordCount won't work, you can try
>> to
>> call .MoveLast in order to populate this value.
>
> I think you are thinking of a DAO trick: with ADO, navigating MoveLast
> will not change the RecordCount property value. In fact, using a
> server-side forward only cursor with a read only lock type with Jet,
> the RecordCount will be -1 and attempting to MoveLast will generate a
> run-time error.
>
> Jamie.
>
> --
>
>