Bitwise And

[Sam Hobbs]

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.

 

[Sam Hobbs]

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.

 

[david epsom dot com dot au]

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)

 

[Jamie Collins]

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

--

 

[david epsom dot com dot au]

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)