Missing Column Using QueryTables.Add

[Tieu]

Hi,

I am having problem with QueryTables.Add as follow


....
strSQL = "Select STRING1, NUMBER1, STRING2 from myTable"
Set rs = cn.Execute(strSQL)
....

Set qTable = ActiveSheet.QueryTables.Add( rs, RangeStart)

....

I only see STRING1, STRING2 columns. NUMBER1 column is missing. However, If
I change the strSQL to:
Select STRING1, DIGITS(NUMBER1), STRING2 from myTable
(where DIGITS is an AS400 SQL function to convert number to string)
Then I can see all 3 columns. Is there any QueryTable property that I need
to change, so I can get all column without converting anythign to string.
Thanks in advance.

 

[NickHK]

So first, rs.fields.count=2 ?
Then using DIGITS, rs.fields.count=3 ?

NickHK

 

[Tieu]

the rs.fields is 3 in both cases. The problem is at the QueryTable. When
adding the rs to QTable, somehow the 2 numeric fields didn't get added over.
Thanks.

Tieu

 

[NickHK]

Well, if you 3 fields in your recordset, I don't see how it can disappear
from the WS.
What do you get if you do not use an RS but put it directly into a
QueryTable ?

NickHK

 

[Tieu]

The QueryTable has been always worked correctly. However, there's a reason
that I have to use ADO. I created a SQL UDF which calls a RPG program inside
AS400. This has something to do with user access. And since I already got my
QueryTable setup, I just want to pass ADO to QueryTable via Add Method.
Anyway, I guess I will have to convert everything to string. I would use ADO
for SQL UDF and the rest on QueryTable. Thanks.

Tieu