A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Bitwise And



 
 
Thread Tools Display Modes
  #21  
Old October 6th, 2004, 09:35 AM
david epsom dot com dot au
external usenet poster
 
Posts: n/a
Default


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)








  #22  
Old October 6th, 2004, 02:23 PM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

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" david@epsomdotcomdotau wrote in message
...

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"



  #23  
Old October 7th, 2004, 04:13 AM
david epsom dot com dot au
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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" david@epsomdotcomdotau wrote in message
...

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"





  #24  
Old October 7th, 2004, 10:49 PM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

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" david@epsomdotcomdotau wrote in message
...
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.



  #25  
Old October 8th, 2004, 12:57 AM
david epsom dot com dot au
external usenet poster
 
Posts: n/a
Default

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)



"Sam Hobbs" wrote in message
...
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" david@epsomdotcomdotau wrote in message
...
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.





  #26  
Old October 8th, 2004, 02:08 AM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default

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

--
HTH
Van T. Dinh
MVP (Access)




"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
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)





  #27  
Old October 8th, 2004, 02:38 AM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default

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"???



--
HTH
Van T. Dinh
MVP (Access)


"Sam Hobbs" wrote in message
...
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.




  #28  
Old October 8th, 2004, 02:50 AM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default

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.

--
HTH
Van T. Dinh
MVP (Access)


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
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....



  #29  
Old October 8th, 2004, 03:02 AM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default

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

--
HTH
Van T. Dinh
MVP (Access)


"Van T. Dinh" wrote in message
...
(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.

--
HTH
Van T. Dinh
MVP (Access)



  #30  
Old October 8th, 2004, 04:22 AM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

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.


"Van T. Dinh" wrote in message
...
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"???



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
bitwise XOR calc Luke Worksheet Functions 1 September 1st, 2004 11:31 AM


All times are GMT +1. The time now is 02:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.