Problem in openrecordset method

[TTThio]

Hi,

could someone let me know what is wrong with this code, that when I stepped into it to debug, always got error message 13 (Type Mismatch) after the sSQL.
My table is a linked table from Oracle database.

Thanks ahead.

Sub try()
Dim db As Database
Dim rs As Recordset
Dim sSQL As String
Dim var As String

Set db = CurrentDb
sSQL = "Select Opty_Type from Opportunity WHERE Opty_Type = 'Lost'"

Set rs = db.OpenRecordset(sSQL)

While Not rs.EOF
var = var & rs("Opty_Type&quot & Chr$(13)

Wend

Debug.Print var
rs.Close

End Sub

 

[JoeMiller]

Are you sure that Opty_Type does not relate to a number? Oftentimes tables are made that relate an ID, to a description, so maybe you need to be putting the ID for Lost instead of Lost itself.

Just a guess, HTH. Joe Miller
joe.miller@flotech.net

 

[TTThio]

I've tried to select couple fields other than Opty_Type, for exp. I tried to pull Opty_Desc, Created_Date, still got the same error message.

Wondering if it has something to do with it as linked table?

 

[JoeMiller]

Possibly, not sure, your code looks fine to me. Go into design view of the linked tables and see what datatype you're selecting on. Maybe thatwill give you a clue.
Joe Miller
joe.miller@flotech.net

 

[TTThio]

Yes, I did it already before declaring the variables. Most is text type, so string will do. I'm lost....

 

[TTThio]

I found the problem,
just for info, it's all caused by existance of reference to ADO library, where it's supposed to be only DAO.