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 |
#31
|
|||
|
|||
I think you are correct that the Flags value is not all bit flags. However
for system objects the Flags field are bit values. The sample Microsoft code shows how to test for system objects. I am conficent I can rely on the sample code to understand how to use the Flags field, at least for the purpose of testing for system objects. "david epsom dot com dot au" david@epsomdotcomdotau wrote in message ... 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. |
#32
|
|||
|
|||
Actually, there are a few articles in the MSKB using "MSys". Here is an
example: http://support.microsoft.com/?id298174 -- HTH Van T. Dinh MVP (Access) "Sam Hobbs" wrote in message ... 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. |
#33
|
|||
|
|||
Yes, thank you.
I did overreact a bit, but I still don't want to use that. Note that that sample uses DAO and the TableDefs collection. I consider the Flags solution to be more flexible and more likely to be supported by Microsoft in the future. I am nearly certain that Microsoft is advising us to avoid using DAO in new projects. However since the "MSys"prefix is used in Microsoft documentation (at least KB articles) it is likely to continue to work, at least as long as DAO works. "Van T. Dinh" wrote in message ... Actually, there are a few articles in the MSKB using "MSys". Here is an example: http://support.microsoft.com/?id298174 |
#34
|
|||
|
|||
You might like to read the following articles:
http://www.trigeminal.com/usenet/usenet025.asp?1033 and, espcially for System Tables: http://www.trigeminal.com/usenet/usenet017.asp?1033 For me, if I use JET Back-End, I stick to DAO. If I use Microsoft SQL Server Back-End, I switch to ADO as much as I can (MDB/MDE format, not ADP/ADE). (I am _not_ trying to convince you to use DAO/MSys, though.) -- HTH Van T. Dinh MVP (Access) "Sam Hobbs" wrote in message ... Yes, thank you. I did overreact a bit, but I still don't want to use that. Note that that sample uses DAO and the TableDefs collection. I consider the Flags solution to be more flexible and more likely to be supported by Microsoft in the future. I am nearly certain that Microsoft is advising us to avoid using DAO in new projects. However since the "MSys"prefix is used in Microsoft documentation (at least KB articles) it is likely to continue to work, at least as long as DAO works. |
#35
|
|||
|
|||
As for "What does DAO have that ADO/ADOx/JRO do not have (and might never
have!)", I suspect that there actually are ways to do most of them in a similar way, just not the same way. I don't know Access well enough to suggest alternatives, and that would be off-topic for this discussion. However if there are reasonable alternatives, then that web page should include that information as well. If there has not been an effort by the author to find alternatives, then the usefullnes of that whole page is questionable. I have been trying to avoid increasing the size of this discussion unnecessarily, so I was vague when I said that use of the Flags field is more flexible. It is more flexible in the manner that it is possible for organizations other than Microsoft to designate an object as being a system object or hidden and if they do they should not use the "MSys" prefix; at least, that seems to designate the object as being a Microsoft system object. Use of the Flags field would work for objects designated (in manners supported by Microsoft, right?) to be a system and/or hidden object, whereas the "MSys" prefix would not work in all situations. "Van T. Dinh" wrote in message ... You might like to read the following articles: http://www.trigeminal.com/usenet/usenet025.asp?1033 and, espcially for System Tables: http://www.trigeminal.com/usenet/usenet017.asp?1033 For me, if I use JET Back-End, I stick to DAO. If I use Microsoft SQL Server Back-End, I switch to ADO as much as I can (MDB/MDE format, not ADP/ADE). (I am _not_ trying to convince you to use DAO/MSys, though.) |
#36
|
|||
|
|||
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! -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Sam Hobbs" wrote in message ... As for "What does DAO have that ADO/ADOx/JRO do not have (and might never have!)", I suspect that there actually are ways to do most of them in a similar way, just not the same way. I don't know Access well enough to suggest alternatives, and that would be off-topic for this discussion. However if there are reasonable alternatives, then that web page should include that information as well. If there has not been an effort by the author to find alternatives, then the usefullnes of that whole page is questionable. I have been trying to avoid increasing the size of this discussion unnecessarily, so I was vague when I said that use of the Flags field is more flexible. It is more flexible in the manner that it is possible for organizations other than Microsoft to designate an object as being a system object or hidden and if they do they should not use the "MSys" prefix; at least, that seems to designate the object as being a Microsoft system object. Use of the Flags field would work for objects designated (in manners supported by Microsoft, right?) to be a system and/or hidden object, whereas the "MSys" prefix would not work in all situations. "Van T. Dinh" wrote in message ... You might like to read the following articles: http://www.trigeminal.com/usenet/usenet025.asp?1033 and, espcially for System Tables: http://www.trigeminal.com/usenet/usenet017.asp?1033 For me, if I use JET Back-End, I stick to DAO. If I use Microsoft SQL Server Back-End, I switch to ADO as much as I can (MDB/MDE format, not ADP/ADE). (I am _not_ trying to convince you to use DAO/MSys, though.) |
#37
|
|||
|
|||
.... if it is good enough for Doug, it is good enough for me, too smile.
Note that soon after MichKa wrote this article, Microsoft moved from ADO to ADO .Net and the emphasis is ADO .Net. All the improvements have been on ADO .Net so I don't think (not 100%, though) there much improvements in ADO since then. -- HTH Van T. Dinh MVP (Access) "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! -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) |
#38
|
|||
|
|||
It is not good enough for me, but that would make a good discussion so
perhaps one of us will start one; hopefully not in this discussion though. "Douglas J. Steele" wrote in message ... If MichKa says it can't be done, that's good enough for me! |
#39
|
|||
|
|||
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) "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. "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. |
#40
|
|||
|
|||
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) |
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 |