Missing Column Using QueryTables.Add
 

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.

Missing Column Using QueryTables.Add
 

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

NickHK

"Tieu" wrote in message
...
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.

Missing Column Using QueryTables.Add
 

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

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

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

Missing Column Using QueryTables.Add
 

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" wrote in message
...
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

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

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

Missing Column Using QueryTables.Add
 

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

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

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

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

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