assume that creation is not the same thing as executing.

Execute is something like Docmd.OpenQuery or db.Execute
or cnn.Execute.

Creating a query can be done either in Access, using the
"Query By Example design grid" (which creates a Jet/Access
query), or by using "Data Definition Language" like this:

dim cnn as ADODB.Connection
Set cnn = Application.CurrentProject.Connection
cnn.Execute "Create View AnsiQuery1 as SELECT * FROM TABLE1"

I just checked: I can't see AnsiQuery1, but I can write sql
like "Select * from Ansquery1"

(david)


"Sam Hobbs" wrote in message
...
Oh, I should have recognized "FWIW" but I did not. My mind was sure blank
but now it seems very obvious.

Thank you for this too. I don't know how to create queries using ADO. I
assume I can figure that out and I will eventually. Perhaps the meaning of
what you are saying is also obvious and I will realize it later. It does
sound interesting and useful. I am certainly interested in knowing what an
ANSI query looks like in the MSysObjects table. When you say "create an
ADO
connection to an A2K database, and use the connection to create a query",
I
assume that creation is not the same thing as executing. I assume you mean
that creation is done once and then the query can be executed without ADO,
but I don't know how.



"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
:~) For What It's Worth

In your case, worth only a penny I guess :~)

Note: it is possible, using ADO, to create
ANSI queries in a Non-Ansi database. In Access
2000 it is not possible to put the database
into ANSI mode, and any ADO-created ANSI
querydefs are not visible in the Access
database window. But they are still there, and
may be enumerated.

AFAIK (As Far As I Know :~), no-one has ever
discussed the behaviour in Access of ANSI queries
in a (non-ANSI) A2000 database, other than
to say that they are not visible if they exist.

Access/Jet querydefs have two streams: Text
(SQL), and compiled (binary). Both ANSI and
non-ANSI SQL will compile, and the compiled
query is what is used, not the actual Text
stream. So you would expect that compiled
ANSI querydefs might be used interchangeably
with compiled non-ANSI querydefs, but I've
never tried it.

If you create an ADO connection to an A2K database,
and use the connection to create a query, you
apparently get an ANSI query. I know this because
there used to be questions here like: "I used
ADO to create a query in my database. It's there,
but I can't see it in the Access database window.
Why not?"

(david)

"Sam Hobbs" wrote in
message
...
Thank you. That helps; I assume it answers the question of whether BAnd
works in a query.

As for FWIW, I don't know what it is and I can't find it in the Access
documentation or MSDN.

As for ANSI SQL query mode, the documentation says that it applies to
an
entire database. If so, then unfortunately it is not useful for this.
This
is a general-purpose solution for use in many databases; that is, it
needs
to work with the most common Access databases.


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
FWIW, when using 'ANSI' mode, you can use 'BAND'
in queries: (2 BAND 4)