RecordSet methods generates conversion error and SQL results have no BOF And EOF

I have a SQL query that I have executed through the SQL program successfully. I ended up with the data that I combined into one column which has the header (No Column Name).

I attempted the execute this same statement through a Visual Basic program and executing

MsgBox recordstObject.Fields("")

returns the desired results.

However, executing

MsgBox recordstObject.RecordCount

returns

[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting 
the varchar value 'A01' to data type int.

This error also appears when I tried a work around with a combination of a while loop and recordstObject.MoveNext. I've tried checking if there's a BOF or an EOF via

If Not (recordstObject.BOF And recordstObject.EOF) Then
    MsgBox "No 开发者_StackOverflowBegin or End"
Else
    MsgBox "There are records"
End If
recordstObject.MoveNext

And it turns out that the "No Begin or End" MsgBox showed up.

It should be noted that I'm currently working with a result of one but will eventually need to compensate for multiple results.

My sql Statement is

SELECT id + '|' + name + '|DogBreed' FROM breeds WHERE c_id = 9

Which MsgBox recordstObject.Fields("") says 1|Scottish Terr|DogBreed

Suggestions on how to fix this?

Thanks.

I thought an empty recordset shows up with BOF and EOF together - which would mean your test is the wrong way round (ie you're saying If Not Empty Then Msgbox "Empty")

Some connection/locking/marshalling methods do not support the RecordCount property because the records aren't all transferred to the client in one go. However iterating through the recordset should have done the trick.

Not sure why you're getting the type conversion error, unless the driver is guessing an Integer datatype based on the first row it sees and then getting tripped up on subsequent rows. That would be quite weird for SQL Server though - it's more typical on Excel text ODBC drivers.
Can you post your SQL ?