query missing some columns

Why would the following not return the same number of columns I see if I run
"New Crosstab" inside the Access Database? It should return 5 columns but I
only get 3 in my vbscript. Does it have something to do with "TRANSFORM" and
a crosstab query?

const ConnectString = "Provider= Microsoft.Jet.OLEDB.4.0;Data
Source=\\...\Status.mdb"
sql = "select * from [New Crosstab]"

Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.open sql, ConnectString, adOpenKeyset, adLockReadOnly
rows = rs.GetRows

thanks
LJB

Bob
Mon May 23 15:57:48 CDT 2005

ljb wrote:
> Why would the following not return the same number of columns I see
> if I run "New Crosstab" inside the Access Database? It should return
> 5 columns but I only get 3 in my vbscript. Does it have something to
> do with "TRANSFORM" and a crosstab query?

No. A query should return the correct number of columns whether it is run
inside Access or run from another client program.

Can you provide us with a small set of data and a query to reproduce this?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

ljb
Tue May 24 08:25:38 CDT 2005


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uoZmUo9XFHA.1660@TK2MSFTNGP10.phx.gbl...
> ljb wrote:
> > Why would the following not return the same number of columns I see
> > if I run "New Crosstab" inside the Access Database? It should return
> > 5 columns but I only get 3 in my vbscript. Does it have something to
> > do with "TRANSFORM" and a crosstab query?
>
> No. A query should return the correct number of columns whether it is run
> inside Access or run from another client program.
>
> Can you provide us with a small set of data and a query to reproduce this?
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
I wish I could provide some data easily. Right now it looks like the two
columns I'm missing contain a few null values. It must be the null values
that causing the problem. Perhaps a strategic use of nz() will fix it. I'll
work on it and report back.

thanks
LJB

ljb
Wed May 25 08:32:07 CDT 2005


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uoZmUo9XFHA.1660@TK2MSFTNGP10.phx.gbl...
> ljb wrote:
> > Why would the following not return the same number of columns I see
> > if I run "New Crosstab" inside the Access Database? It should return
> > 5 columns but I only get 3 in my vbscript. Does it have something to
> > do with "TRANSFORM" and a crosstab query?
>
> No. A query should return the correct number of columns whether it is run
> inside Access or run from another client program.
>
> Can you provide us with a small set of data and a query to reproduce this?
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

It turns out ADO and Visual Basic do not like SQL "LIKE". My CrossTab query
contained another query with LIKE. It works in Access but fails silently in
ADO. Someone please tell me where that's documented! I was able to fix the
problem using Right(), Left() functions and IN a list.

thanks
LJB

Bob
Wed May 25 08:50:06 CDT 2005

ljb wrote:
>
> It turns out ADO and Visual Basic do not like SQL "LIKE".

Not true.

I suspect you've run into a "wildcard" issue. Queries run inside Access
require the Jet wildcards (* and ?) to be used as wildcards in LIKE
comparisons. ADO requires the use of ODBC wildcards (% and _), regardless of
the backend database being used (the Jet OLE DB provider translates them
into the Jet wildcards). It is counterintuitive, but even saved queries are
subject to this rule. Saved queries run by ADO must contain the ODBC
wildcards, not the Jet wildcards.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

ljb
Wed May 25 09:17:46 CDT 2005


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eenIqCTYFHA.228@TK2MSFTNGP12.phx.gbl...
> ljb wrote:
> >
> > It turns out ADO and Visual Basic do not like SQL "LIKE".
>
> Not true.
>
> I suspect you've run into a "wildcard" issue. Queries run inside Access
> require the Jet wildcards (* and ?) to be used as wildcards in LIKE
> comparisons. ADO requires the use of ODBC wildcards (% and _), regardless
of
> the backend database being used (the Jet OLE DB provider translates them
> into the Jet wildcards). It is counterintuitive, but even saved queries
are
> subject to this rule. Saved queries run by ADO must contain the ODBC
> wildcards, not the Jet wildcards.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

You are correct and that explains why I didn't see any error message.

thanks
LJB