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
  #41  
Old October 12th, 2004, 05:03 AM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

As Van has said, your message (I think) did get through. I replied to it,
but if you can't find my replies then let us know. You can email me directly
offline from the list; just modify my email address in the manner specified
(it is samuel at socal dot rr dot youknow). Or use SoCalSam at Gmail.

You seem to have missed the part where I said that the Flags field is not
entirely simply bit fields, as you have been told, but for the purpose of
this discussion, the relevant bits are single independent bits.


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



  #42  
Old October 12th, 2004, 05:14 AM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

I apologize for being critical and such of DAO. The important thing is that
I wanted to know if there is a slightly simpler way to do what I was doing.
So regardless of the usefulness of DAO, I am asking about an alternative
that also works.


"Douglas J. Steele" wrote in message
...
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!



  #43  
Old October 13th, 2004, 02:32 AM
david epsom dot com dot au
external usenet poster
 
Posts: n/a
Default

Thanx Van - I'm connecting to the MS server, but I didn't
see my message or your reply. It's on google, so I've just
been back and read the items I'm missing.

This is what I conclude:

*) BAND can only be used by an 'ANSI' mode database, or by
ADO.

*) BAND can't be saved in a saved querydef unless the querydef
is saved/flagged as an 'ANSI' mode view or procedure.

*) 'ANSI' queries can be used by ADO, or by Access 2003 if the
database is in 'ANSI' mode. 'ANSI' queries can also (and this
is the bit I didn't know) be used by ordinary Access/DAO if
the SQL is also Jet complient SQL, but not otherwise.

*) ADO can use querydefs saved in 'Jet' mode, and DAO can use
querydefs saved in 'ANSI' mode, but only if the SQL is valid
in the current mode. Either two seperate binary streams are
saved or, more likely, the SQL is always recompiled when used
in the other mode.

Also, some general comments about 'ANSI' mode:

*) I only mean 'ANSI' as a name for the alternative Jet syntax.
I don't mean that it actually meets any ANSI standard. In fact,
I think the whole thing is a bit misleading: Jet 2.0, which
had atomic SQL statements, seems to me to be much more ANSI 92
complient than Jet 4.0 in any mode. Jet 4.0 can accept Unix/C
wild cards (wheee!) instead of DOS wildcards, but SQL statements
aren't atomic. That's not ANSI SQL.


*) The interesting thing about the 'ANSI 89' and 'ANSI 92'
labels is that 'LEFT JOIN' and 'RIGHT JOIN' were standardised
by ANSI 92. Access 2 (1994) never really liked common ANSI
89 SQL (with the join criteria in the WHERE clause), and most
ANSI 89 systems never really liked Jet SQL (with 'LEFT JOIN'
and 'RIGHT JOIN').

*) It was well known :~) that you couldn't see 'ANSI' queries
in Access 2000, that you could see them but not use them in
Access 2002, and that Access 2003 could be put into 'ANSI'mode.

*) I think that the help you quote is misleading. The 'ANSI'
queries created by ADO DDL are just as 'ANSI' as those created
by ADOX or by Access 2003 in 'ANSI' mode. The queries I created
were (a) not visible in Access 2000, (b) flagged differently
in the system table, and (c) included syntax not legal in Jet
SQL. I can create the same view using ADOX or Access 2003, and
the behaviour, visibility, and flags are the same as when I use
ADO DDL.

BTW, 'Procedure' is a legal key word in 'Jet' SQL. And even in
A97, the database window could list Update queries, Union queries,
ODBCdirect queries,Append queries, views etc etc. I can think
of lots of reasons for not listing 'ANSI' procedures in the
database window (too confusing for users, not appropriate for
target market, untested etc etc), but none of them map to "there
is no equivalent to Proc in Access" :~)


(david)


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




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








  #44  
Old October 13th, 2004, 03:58 PM
Jamie Collins
external usenet poster
 
Posts: n/a
Default

"david epsom dot com dot au" david@epsomdotcomdotau wrote ...

'Procedure' is a legal key word in 'Jet' SQL. And even in
A97, the database window could list Update queries, Union queries,
ODBCdirect queries,Append queries, views etc etc. I can think
of lots of reasons for not listing 'ANSI' procedures in the
database window (too confusing for users, not appropriate for
target market, untested etc etc), but none of them map to "there
is no equivalent to Proc in Access" :~)


You didn't mention using ADO's OpenSchema method when used with the
adSchemaProcedures enum. I only know Jet, not MS Access, but it seems
to return created using CREATE PROCEDURE via ADO and regular 'stored
queries' (or whatever you guys call them) created using the MS Access
UI but not those created using CREATE VIEW.

Jamie.

--
  #45  
Old October 14th, 2004, 04:19 AM
david epsom dot com dot au
external usenet poster
 
Posts: n/a
Default

Yes, the ADO distinction between Procedures and Views is
different than the Jet distinction between TableDefs and
QueryDefs.

In a 'relational' database system, a Table and a View are
the same thing: a Table is a 'Relation' is a View.

So ADO puts Jet Querydefs with the tables if they are select
queries or with the procedures if they are action queries.

Actually, the division is little bit arbitrary, because
sometimes views get flagged as procedures, and wind up in
the procedure collection instead of the view collection.

(david)


"Jamie Collins" wrote in message
om...
"david epsom dot com dot au" david@epsomdotcomdotau wrote ...

'Procedure' is a legal key word in 'Jet' SQL. And even in
A97, the database window could list Update queries, Union queries,
ODBCdirect queries,Append queries, views etc etc. I can think
of lots of reasons for not listing 'ANSI' procedures in the
database window (too confusing for users, not appropriate for
target market, untested etc etc), but none of them map to "there
is no equivalent to Proc in Access" :~)


You didn't mention using ADO's OpenSchema method when used with the
adSchemaProcedures enum. I only know Jet, not MS Access, but it seems
to return created using CREATE PROCEDURE via ADO and regular 'stored
queries' (or whatever you guys call them) created using the MS Access
UI but not those created using CREATE VIEW.

Jamie.

--



 




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 10:15 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.