Bitwise And

[david epsom dot com dot au]

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]

I used your "Data Definition Language" sample code and it did create
AnsiQuery1. So thank you for showing that; it saved me a little time and I
can use that sometime. The AnsiQuery1 query was created and works but it
also is shown among the other queries. So I tried creating the query that
Van T. Dinh provided, as in:

Create View AnsiQuery2 as SELECT (4 BAND 4) As Test FROM Table1

That query is also created and also is shown among the other queries.
However it does not work; Access says:

Syntax error (missing operator) in query expression '(4 BAND 4)'.

 

[david epsom dot com dot au]

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.

(david)

 

[Sam Hobbs]

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]

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

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)

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.

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.

 

[Van T. Dinh]

(not 100% sure but ...)

I don't think either of them create an ANSI-92. More likely, they are JET
SQL syntax which closely conforms to ANSI-89 Level 1 Specification but not
(completely) compliant to ANSI-89 Level 1.

According to Help, you can only create ANSI-92 Level 1 (well, close to it)
in Access using ADOX. Since it did not mention ADODB, I guess if the CREATE
VIEW running in ADODB would be JET SQL, i.e. closely conforming to ANSI-89
Level 1.

I confirmed what Sam obverved also: the CREATE VIEW for simple SQL Strings
we have been testing actually showed up in the Queries tab. However, I
tested in A2002 and I guess Sam did. Perhaps, the difference is that you
tested with A2000 and all of these things were completely new in A2000 and
they may not work correctly. Have you tried open the Queries tab and do a
Refresh?

IIRC, CREATE PROCEDURE won't show the Proc in either A2000 or A2002 (MDB)
since there is no equivalent to Proc in Access.

 

[Van T. Dinh]

I think the Flag values depend on the type of the Query. In my test
database (which has a bit of rubbish), I found the following values for
Queries:

0
3 Hidden Queries (SQL Strings in Forms)
16 Cross-Tab Query
96 DDL Query (ALTER TABLE ...)
112 Pass-Through Queries
268435456 CREATE VIEW
268435536 CREATE PROCEDURE

Re-reading your orginal question, if you want to check whether the Table is
a System Object (Table), you can check whether the name starts with
"MSys"???

 

[Van T. Dinh]

I think your tests confirm that both "ansiquery1" and "ansiquery2" are
simply JET syntax.

The first 2 tests simply use ADO connection which we know that the new JET
syntax (BAND) works.

The 3rd test didn't work because DLookUp uses (IIRC) internal database
access (similar to DAO) which is also used by the Access interface and new
JET syntax (BAND) is not recognised.

The 4th test only used old JET syntax and therefore, worked fine regardless.

If you have A2002, check Access Help topic "About ANSI SQL query mode". I
am not sure whether this one is in A2000 Help.

 

[Van T. Dinh]

Small correction for the last paragraph: CREATE PROCEDURE ... where the end
result is a parametrised Query shows up in the Queries tab.

 

[Sam Hobbs]

Thank you for all your analyses. The additional flag values are interesting.
The following shows the relevant constants that I think apply.

3 DB_SYSTEMOBJECT and DB_HIDDENOBJECT
0x10 DB_QCROSSTAB
0x60 DB_QDDL
0x70 DB_QSQLPASSTHROUGH
0x10000000 (unknown)
0x10000050 (unknown) and DB_QMAKETABLE

I definitely don't want to use the "MSys" prefix solution. That is a common
solution, but I don't see any Microsoft code using it. I do see Microsoft
code using the Flags field and DB_SYSTEMOBJECT constant as a solution. I
avoid using undocumented and less flexible solutions whenever possible.

 

[Sam Hobbs]

I think you are correct that the Flags value is not all bit flags. However
for system objects the Flags field are bit values. The sample Microsoft code
shows how to test for system objects. I am conficent I can rely on the
sample code to understand how to use the Flags field, at least for the
purpose of testing for system objects.

 

[Van T. Dinh]

Actually, there are a few articles in the MSKB using "MSys". Here is an
example:

http://support.microsoft.com/?id298174

 

[Sam Hobbs]

Yes, thank you.

I did overreact a bit, but I still don't want to use that. Note that that
sample uses DAO and the TableDefs collection. I consider the Flags solution
to be more flexible and more likely to be supported by Microsoft in the
future. I am nearly certain that Microsoft is advising us to avoid using DAO
in new projects. However since the "MSys"prefix is used in Microsoft
documentation (at least KB articles) it is likely to continue to work, at
least as long as DAO works.

 

[Van T. Dinh]

You might like to read the following articles:

http://www.trigeminal.com/usenet/usenet025.asp?1033

and, espcially for System Tables:

http://www.trigeminal.com/usenet/usenet017.asp?1033

For me, if I use JET Back-End, I stick to DAO. If I use Microsoft SQL
Server Back-End, I switch to ADO as much as I can (MDB/MDE format, not
ADP/ADE).

(I am _not_ trying to convince you to use DAO/MSys, though.)

 

[Sam Hobbs]

As for "What does DAO have that ADO/ADOx/JRO do not have (and might never
have!)", I suspect that there actually are ways to do most of them in a
similar way, just not the same way. I don't know Access well enough to
suggest alternatives, and that would be off-topic for this discussion.
However if there are reasonable alternatives, then that web page should
include that information as well. If there has not been an effort by the
author to find alternatives, then the usefullnes of that whole page is
questionable.

I have been trying to avoid increasing the size of this discussion
unnecessarily, so I was vague when I said that use of the Flags field is
more flexible. It is more flexible in the manner that it is possible for
organizations other than Microsoft to designate an object as being a system
object or hidden and if they do they should not use the "MSys" prefix; at
least, that seems to designate the object as being a Microsoft system
object. Use of the Flags field would work for objects designated (in manners
supported by Microsoft, right?) to be a system and/or hidden object, whereas
the "MSys" prefix would not work in all situations.

 

[Douglas J. Steele]

I think you'll find that there are few people more knowledgable about how
Access works than Michael Kaplan, the author of the web pages in question.
If MichKa says it can't be done, that's good enough for me!

 

[Van T. Dinh]

.... if it is good enough for Doug, it is good enough for me, too <smile>.

Note that soon after MichKa wrote this article, Microsoft moved from ADO to
ADO .Net and the emphasis is ADO .Net. All the improvements have been on
ADO .Net so I don't think (not 100%, though) there much improvements in ADO
since then.

 

[Sam Hobbs]

It is not good enough for me, but that would make a good discussion so
perhaps one of us will start one; hopefully not in this discussion though.

 

[david epsom dot com dot au]

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)

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.

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.

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.

 

[Van T. Dinh]

Hi David

We did get your post previously and here is my replies to your previous
posts in case you missed them.


****Quote 1****
(not 100% sure but ...)

I don't think either of them create an ANSI-92. More likely, they are JET
SQL syntax which closely conforms to ANSI-89 Level 1 Specification but not
(completely) compliant to ANSI-89 Level 1.

According to Help, you can only create ANSI-92 Level 1 (well, close to it)
in Access using ADOX. Since it did not mention ADODB, I guess if the CREATE
VIEW running in ADODB would be JET SQL, i.e. closely conforming to ANSI-89
Level 1.

I confirmed what Sam obverved also: the CREATE VIEW for simple SQL Strings
we have been testing actually showed up in the Queries tab. However, I
tested in A2002 and I guess Sam did. Perhaps, the difference is that you
tested with A2000 and all of these things were completely new in A2000 and
they may not work correctly. Have you tried open the Queries tab and do a
Refresh?

IIRC, CREATE PROCEDURE won't show the Proc in either A2000 or A2002 (MDB)
since there is no equivalent to Proc in Access. This was correctly later as
the Procedures that are equivalent to Parametrised Queries do appear as
Queries in the Queries tab (at least in A2002).


****Quote 2****
I think your tests confirm that both "ansiquery1" and "ansiquery2" are
simply JET query (syntax).

The first 2 tests simply use ADO connection which we know that the new JET
syntax (BAND) works.

The 3rd test didn't work because DLookUp uses (IIRC) internal database
access (similar to DAO) which is also used by the Access interface and new
JET syntax (BAND) is not recognised.

The 4th test only used old JET syntax and therefore, worked fine regardless.

If you have A2002, check Access Help topic "About ANSI SQL query mode". I
am not sure whether this one is in A2000 Help.
****End Quotes****

--
Cheers
Van T. Dinh
MVP (Access)