Missing Column Using QueryTables.Add

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

NickHK

"Tieu" <Hey.D@Not-Spam.Me> wrote in message
news:exZMPVbtGHA.1224@TK2MSFTNGP03.phx.gbl...
> 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.
>
>
>
>

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

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" <Hey.D@Not-Spam.Me> wrote in message
news:%23NFw6njtGHA.4648@TK2MSFTNGP04.phx.gbl...
> 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.
> > >
> > >
> > >
> > >
> >
> >
>
>

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