Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    ANSI SQL 92 (Access XP (2002))

    Is there any (Microsoft) document that describes the (sub?)set of ANSI SQL 92 statements, supposed to be supported in ACCESS 2002. More specifically I want to know the syntax of the SELECT name CASE WHEN a THEN b ..... END statement. I get an -1001 error on this one ( no help or information available)

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ANSI SQL 92 (Access XP (2002))

    (Please feel free to correct me if I'm mistaken with this...)

    AFAIK, the CASE statement is not supported in Access - only platforms that support T-SQL (such as SQL Server).

    In Access SQL, you have several choices. Depending on your needs, you can use the IIF or SWITCH functions.

    Another nice thing is that you can write your own custom functions in a Module and reference them in a Query or other SQL Statement in Access.

    Hope this helps

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ANSI SQL 92 (Access XP (2002))

    You might take a look at MS Knowledge Base article 287417 which tells you which statements will cause error messages. SELECT name CASE isn't on that list it appears, so it should work. However, there are some strange things that go on when you try to use the grid with such statements. As Mark points out, the IIF statement is supported in Access, which is generally more powerful than the CASE statement, so I tend to use that. Note however that if you are working with an ADP rather than MDB, then the SQL syntax supported is that of SQL Server 2000, which is much closer to ANSI 92. Hope this helps a bit.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Re: ANSI SQL 92 (Access XP (2002))

    I'm flabbergasted. According to MS " ... ANSI-92 provides new reserved words, syntax rules, and wildcard characters that enhance your ability to create queries, filters, and SQL statements. This mode conforms closely to the ANSI-92 Level 1 specification but is not ANSI-92 Level 1 compliant. This query mode has more of the ANSI syntax, and the wildcard characters conform to the SQL specification ...". Checking the KBS article, I discovered that at the latest tally 109 ANSI reserved words are known to generate the -1001 error. MS must have some strange ideas about a standard that is closely conformed to!
    Although MS has confirmed that this is a problem, the article has not been reviewed for more than a year! I fear there will not be a solution in the near nor distant future.

    Furthermore, exploring the MS online literature, there is hardly any reference to the possible support of SQL-92 by ACCESS XP. Which is a rather different tale than the Marketing Material tells us. Also I can nowhere find what the "Closely" exactly means: there is no positive list of statements and words, describing what exactly is implemented in ACCESS.

    Given the circumstances, Is there any practical use of this feature possible?

    Or should we implement MS's workaround (cfr KBS article): Clear the check box under SQL Server Compatibility Syntax (ANSI 92)? Which anyhow is the default option.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ANSI SQL 92 (Access XP (2002))

    In all honesty, not much. If you are using a Jet back-end, there may be a few things that you could do with this feature that are impossible with the standard Jet SQL, but in 10 years of work with Access, I haven't found any yet. If you are using a more robust backend such as the MSDE or SQL Server, then you have a full featured SQL syntax at your disposable. You can also use Pass-through or ODBC Direct queries to work with linked tables when necessary. Are you trying to move a project into SQL that was written for another database?
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Re: ANSI SQL 92 (Access XP (2002))

    Wendell,

    Just a laudable albeit naive management iniated attempt to bring some law and order in the shop. I suspect the hidden agenda is to move away from MS platform. Lack of MS documentation seems to be an additional argument!

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ANSI SQL 92 (Access XP (2002))

    Naive is probably the operable word - I don't know of any database product that fully conforms to ANSI 92 (and doesn't have a bunch of non-standard extensions that make it far more usable). I must confess ignorance when it comes to mySQL, but my impression is that the design tools available with it are pretty primitive compared to what Access (and dBase and Paradox and others) offer. However both Oracle and SQL Server have a bunch of extensions which make the product significantly better, but are not compatible with other database products. As to documentation, I don't see any significant difference in the quality or completeness of Microsoft products compared to other vendors.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •