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