Tuesday, April 20, 2004

 

Conditional Expressions in JET SQL

AKA “Can I use If … Then or Select … Case in a Microsoft Access query?”

Part Two

In Part One of this series, I wrote that in future articles I would try to “delve a little deeper into the question of just what functions are supported directly by the JET database engine”. As I began to research this question, I soon discovered two things …

The first thing that I discovered was that I’d taken on a much more difficult task than I’d anticipated, as I’ve yet to find any documentation describing which functions can and can not be used in JET queries when executed outside of the Microsoft Access environment. There appears to be nothing about this in the Microsoft Access or VBA help files, or in the MSDN (Microsoft Developer Network – http://msdn.microsoft.com) library. I found a few references that lead me to believe that there may be something relevant in the MDAC SDK, but haven’t had an opportunity to follow up on those yet – watch this space.

The second thing that I discovered was that some of what I wrote in Part One of this series may not be entirely accurate, or at least, requires some further clarification. The following is a quote from the Microsoft Access 2003/VBA help topic “SQL Expressions”, which you’ll find under “Microsoft Jet SQL Reference” in the help table of contents …

“The Microsoft Jet database engine uses the Microsoft Visual Basic for Applications (or VBA) expression service to perform simple arithmetic and function evaluation. All of the operators used in Microsoft Jet SQL expressions (except Between, In, and Like) are defined by the VBA expression service. In addition, the VBA expression service offers over 100 VBA functions that you can use in SQL expressions. For example, you can use these VBA functions to compose an SQL query in the Microsoft Access query design view, and you can also use these functions in an SQL query in the DAO OpenRecordset method in Microsoft Visual C++, Microsoft Visual Basic, and Microsoft Excel code.”

In light of this information, it’s clearly not entirely accurate to talk about “functions supported directly by the Microsoft JET database engine”. It would appear that, in addition to trying to establish just which functions can and can not be used in JET SQL statements executed outside the Microsoft Access environment, I now also need to attempt to clarify just what the relationship is between the JET database engine and the VBA expression service. Watch this space again …

In the meantime, I’ll resort to some good old-fashioned trial-and-error. The Microsoft Access 2003/VBA help files include an alphabetical list of VBA functions. There are six functions with names beginning with the letter “A”, namely Abs, Array, Asc, AscB, AscW, and Atn. I can’t think of a means (let alone a reason) to use the Array function in a query, at least not without combining it with other functions, so I’ll ignore that one for now. Here’s the SQL for a query that includes each of the other five functions …

SELECT 
    Abs([TestLong]) AS TestAbs, 
    Asc([TestText]) AS TestAsc, 
    AscB([TestText]) AS TestAscB, 
    AscW([TestText]) AS TestAscW, 
    Atn([TestLong]) AS TestAtn
FROM tblTest
WITH OWNERACCESS OPTION;

As the names imply, “TestLong” is a long integer field, and “TestText” is a text field.

This query is not intended to return a meaningful result. The point here is not to demonstrate the use of these particular functions, but simply to investigate whether they can be used in queries executed outside the Microsoft Access environment.

Having saved this query (I called it “qryTestFunctionsA”) I next needed some non-Access test code to exercise it. I used C#, simply because that’s what I happen to be working with these days when I’m not working with Access. If you prefer, you can adapt the code from Part One of this series and use it in Microsoft Excel, Microsoft Visual Basic, or Microsoft Word …

using System;

namespace TestFunctionsA
{
    /// 
    /// Summary description for Class1.
    /// 
    Class Class1
    {
        /// 
        /// The main entry point for the application.
        /// 
        [STAThread]
        static void Main(string[] args)
        {
            System.Data.OleDb.OleDbConnection TheConnection =
                new System.Data.OleDb.OleDbConnection();
            try
            {
                System.String strProvider = @"Microsoft.Jet.OLEDB.4.0";

                // Change this to the actual location of your test MDB.
                System.String strDataSource =
                    @"C:\Documents and Settings\Brendan Reynolds\My Documents\Blog\Test2.mdb";

                System.String strConnectionString =
                    @"Provider = " + strProvider + @"; " + @"Data Source = " + strDataSource;
                TheConnection.ConnectionString = strConnectionString;
                TheConnection.Open();
                System.Data.OleDb.OleDbCommand TheCommand = new System.Data.OleDb.OleDbCommand();
                TheCommand.CommandText = @"qryTestFunctionsA";
                TheCommand.CommandType = System.Data.CommandType.StoredProcedure;
                TheCommand.Connection = TheConnection;
                System.Data.OleDb.OleDbDataReader TheReader =
                    TheCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                While (TheReader.Read())
                {
                    for (System.Int32 i = 0; i < TheReader.FieldCount; i++)
                    {
                        Console.Write(TheReader.GetValue(i).ToString());
                        if (i < (TheReader.FieldCount - 1))
                        {
                            Console.Write(@", ");
                        }
                        Else
                        {
                            Console.WriteLine();
                        }
                    }
                }
                TheReader.Close();
                Console.Read();
            }
            catch (System.Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                System.Console.Read();
            }
            finally
            {
                TheConnection.Dispose();
            }
        }
    }
}

The code runs without error, and (given my test data) produces the following output …

1, 111, 111, 111, 0.785398163397448
2, 116, 116, 116, 1.10714871779409
3, 116, 116, 116, 1.24904577239825
4, 102, 102, 102, 1.32581766366803
5, 102, 102, 102, 1.37340076694502

The conclusion? We can safely say that the Abs, Asc, AscB, AscW and Atn functions can all be used in Microsoft JET queries outside the Microsoft Access environment.

Future plans for this series include testing more VBA functions, modifying the C# test code to be more generic and reusable, and the continuing hunt for documentation on these issues.


Send feedback on this post to (change the obvious):

Brendan Reynolds
bwnnfb at hotmail dot com

(For the insatiably curious, "bwnnfb" is an acronym for "Blog With No Name Feed-Back")

Copyright © 2004 Brendan Reynolds. All rights reserved.


Comments: Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?