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)