ADO episode

G

Guest

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

Sylvain Lafontaine

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

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

Guest

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 said:
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 said:
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...
 
S

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)


XP said:
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 said:
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 said:
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...
 
J

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.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top