ADO episode

[XP]

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

 

[Sylvain Lafontaine]

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

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

 

[XP]

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.

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)

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

 

[Sylvain Lafontaine]

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)

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.

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)

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

 

[Jamie Collins]

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.

--

 

[Sylvain Lafontaine]

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