Sam, I already posted this once, but I'm not sure if it came through.
I see that the discussion has moved on a bit since. Apologies if I've
missed part of the discursion:
--------------------------
Ok, I've tried some more, and this works (using Band):
?cnn.Execute ("Select (4 band 4) as n from table1").fields(0).value
also:
?cnn.Execute ("select * from ansiquery2").Fields(0).Value
but this doesn't (using Band):
?dlookup("1","ansiquery2")
Although this does (not using Band):
?dlookup("1","ansiquery1")
So my 'Jet' connection can use ANSI queries only if they
use legal 'Jet' syntax.
Obviously, I haven't done this before....
I am familiar with use of "New" in a Dim. Use of it makes "Set"
unnecessary, and it is my understanding that there is not any
No, I wasn't suggesting a difference between 'New' and 'Set',
-- I was postulating a difference between the two connections.
The first method sets cnn to point to the SAME connection
that Access is using. The second method sets cnn to point
to a DIFFERENT (new) connection, created using the connection
string from the first connection, but entirely independent.
In any case, there does not appear to be any difference in
the outcome.
---- I see the further discussion here ----
I am nearly certain that the Flags field consists of binary bit flags.
there something that indicates otherwise?
I was told years ago, here, that it was not entirely binary
bit flags, by someone I respected, but I'm only repeating the
information. I don't have any personal knowledge.
I agree that my ANSI queries are flagged differently from my
Jet Queries, with the value 10000000.
---- I see the further discussion here ----
In my database, which is Access 2000, ANSI queries are not
visible. If you are not using Access 2000, then your ANSI
queries may be visible.
Even if your ANSI queries are visible, it may be that you
can't run them from the Access user interface, unless the
database is in ANSI mode. I certainly can't use DoCmd on
any of the ANSI queries I have created.
(david)
Sam Hobbs said:
I am familiar with use of "New" in a Dim. Use of it makes the "Set"
unnecessary, and it is my understanding that there is not any difference
that is relevant to this. The syntax applies to all objects.
It is interesting that the query is invisible for you but not me. Perhaps
the query that is created is ANSI for you but not for me, but that does not
make a lot of sense.
I am nearly certain that the Flags field consists of binary bit flags. Is
there something that indicates otherwise?
You can try the following query to compare the AnsiQuery1 query to other
queries. For me, most of my queries have a flag of 0 but for the AnsiQuery1
query the flag is 10000000. That value seems to be undocumented.
SELECT Name, Hex([Flags]) AS FlagsHex
FROM MSysObjects
WHERE (((Type)=5))
ORDER BY Type;
However if I have BAnd in a query, even with that flag, then the query
always gets a syntax error, whether I try to run it from the database window
or use a Connection Execute method or a DoCmd.OpenQuery.
Sam Hobbs said:
I am familiar with use of "New" in a Dim. Use of it makes the "Set"
unnecessary, and it is my understanding that there is not any difference
that is relevant to this. The syntax applies to all objects.
It is interesting that the query is invisible for you but not me. Perhaps
the query that is created is ANSI for you but not for me, but that does not
make a lot of sense.
I am nearly certain that the Flags field consists of binary bit flags. Is
there something that indicates otherwise?
You can try the following query to compare the AnsiQuery1 query to other
queries. For me, most of my queries have a flag of 0 but for the AnsiQuery1
query the flag is 10000000. That value seems to be undocumented.
SELECT Name, Hex([Flags]) AS FlagsHex
FROM MSysObjects
WHERE (((Type)=5))
ORDER BY Type;
However if I have BAnd in a query, even with that flag, then the query
always gets a syntax error, whether I try to run it from the database window
or use a Connection Execute method or a DoCmd.OpenQuery.
david epsom dot com dot au said:
Interesting! I am Using Access 2000: I tried both
dim cnn as ADODB.Connection
Set cnn = Application.CurrentProject.Connection
and
dim cnn as new ADODB.Connection
cnn.open Application.CurrentProject.Connection
I expected the first way to give me a Jet/Access
query, and the second way to give me an 'ANSI'
query, but in fact both gave me an Invisible
'ANSI' query. Docmd.OpenQuery didn't see it
either.
You should try the second method: you can also
try using your own connection string, in case
there is something specific in the CurrentProject
Connection. If you are not familiar with ADO
connection strings, try this:
debug.print Application.CurrentProject.Connection
--and delete everything you don't understand.
BTW... I am afraid this might all be a waste of time for
you: I don't think that the system flags are actually
binary bit flags -- I think that they may be a little
bit more confused than that.