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. |
|
|
Thread Tools | Display Modes |
#41
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
bitwise XOR calc | Luke | Worksheet Functions | 1 | September 1st, 2004 11:31 AM |