Friday, April 30, 2004

 

Functions in Microsoft Jet Queries

Functions Beginning with “F”

According to the schedule I outlined a few posts back, I’m supposed to be writing about something other than the list of functions available in Microsoft Jet queries today. However, I’m still feeling a bit twitchy and not very creative or adventurous, as a result of giving up smoking, so I’m going to depart from schedule a little and continue the list with functions beginning with the letter “F”.

I did find (thanks, Mike!:)) a Microsoft Knowledge Base article (http://support.microsoft.com/default.aspx?kbid=294698) that includes a list of functions that can be used in Microsoft Jet queries. However, the results of my tests so far differ somewhat from that list, so it seems worthwhile to continue with the tests.

Of the functions beginning with “F”, the following functions are blocked in “sandbox” mode: FileAttr, FileDateTime, FileLen, and FreeFile.

The Filter function returns an array, and therefore I can’t think of a practical way of testing it in a query, except perhaps by combining it with another function that takes an array as one of its arguments. We haven’t come across such a function as yet. I’ll try to introduce tests for the Filter function (and the Array function, which we skipped over earlier for the same reason) if and when an opportunity arises.

Here’s a query that includes all of the remaining functions with names beginning with “F”:

SELECT Fix([TestDouble]) AS TestFix, Format([TestDate],"Long Date") AS TestFormatVar, Format$([TestDate],"Long Date") AS TestFormatString, FormatCurrency([TestDouble]) AS TestForCur, FormatDateTime([TestDate]) AS TestForDate, FormatNumber([TestDouble]) AS TestForNum, FormatPercent([TestDouble]) AS TestForPer, FV([TestSingle],[TestLong],[TestDouble]) AS TestFV FROM tblTest;

As always in these tests, “TestDouble”, “TestDate”, “TestSingle” and “TestLong” are fields in the table “tblTest”, and their data types, as the names suggest, are Double, Date/Time, Single, and Long Integer, respectively.

This query executed without error when run within Microsoft Access. When run through our JetTest utility, however, the FormatCurrency, FormatDateTime, FormatNumber and FormatPercent functions all failed with ‘undefined function’ error messages. Here’s the final form of the query that executed without error via JetTest:

SELECT Fix([TestDouble]) AS TestFix, Format([TestDate],"Long Date") AS TestFormatVar, Format$([TestDate],"Long Date") AS TestFormatString, FV([TestSingle],[TestLong],[TestDouble]) AS TestFV FROM tblTest;

The functions that we can add to our list of functions that can be used in Microsoft Jet queries executed outside of the Microsoft Access environment, then, are Fix, Format, Format$, and FV.


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.


Wednesday, April 28, 2004

 

Functions in Microsoft Jet Queries

Functions Beginning with "E"

These posts may be a little intermittent and shorter than usual for the next few days. Your humble scribe is currently in the throes of an attempt to give up the dreaded cigarettes, and as a result isn’t really fit for human company – not even electronic company! :( Normal service will resume as soon as possible.

We’ve now reached the letter “E” in our alphabetical investigation into precisely which functions can be used in Microsoft Jet queries outside the Microsoft Access environment. There aren’t many functions with names beginning with “E”, and of those that do exist, the Environ, Environ$, and EOF functions are among the functions that are blocked by the new “sandbox” mode in Microsoft Access 2003. That leaves just the Error, Error$, and Exp functions. Here’s a query that uses each of these three functions. As usual in these examples, “TestInteger” and “TestDouble” are fields in the table “tblTest”, and as the names suggests the data types of these fields are Integer and Double, respectively.

SELECT Error([TestInteger]) AS TestErrVar, Error$([TestInteger]) AS TestErrString, Exp([TestDouble]) AS TestExp FROM tblTest;

This query turned up the first two functions in the list that work in a query executed within the Microsoft Access environment, but failed when the same query was executed using our JetTest utility. Both the Error and Error$ functions worked as expected within the Microsoft Access environment, but failed with “undefined function” error messages when the same query was executed via the JetTest utility. The Exp function worked as expected in both environments. The final form of the query, which executed without error via JetTest, is simply …

SELECT Exp([TestDouble]) AS TestExp FROM tblTest;

So the only “E” function that we can add to our list is the Exp function.


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.


Monday, April 26, 2004

 

A C# Utility to Test Microsoft Jet Queries

In previous posts, we investigated some of the functions that can be used in Microsoft Jet queries, with particular emphasis on those that can be used in queries executed outside the Microsoft Access environment. For testing purposes, we developed a C# console application that I call “JetTest”.

The original version of JetTest was a fairly crude beast. Both the data source (the path and name of the database) and the name of the query to be executed were hard-coded. To run a test against a different query, or a different database, we had to modify and recompile the code. In the second version, we accepted the name of the query as a command-line parameter. This was an improvement, but still a bit clumsy. If we forgot to specify the query name when running the utility, we had to exit the program and start over. So (in addition to some general tidying up of the code) one of the first changes I wanted to make was to handle this a little more gracefully. The relevant code now looks like this:

if (args.Length == 0)
{
    System.String strPrompt = "Please enter the name " +
        "of the query you wish to test, then press " +
        "Enter";
    System.Console.WriteLine (strPrompt);
    strQueryName = System.Console.ReadLine().Trim();
}
else
    strQueryName = args[0].Trim();

if (strQueryName.Length == 0)
{
    System.Console.WriteLine("No query name specified.");
    return;
}

The program first checks the array “args”, which contains any command-line arguments that were passed to the program when it was executed. If the length of the array is zero, no arguments were passed. As in the first version, the program informs the user of the problem, but this time the user doesn’t have to exit the program and start again. The program will accept a query name from the user and continue. If the length of the string variable “strQueryName” is zero at this point, the user has still failed to enter a query name. The only sensible reason I can think of for this to happen is that the user executed the program by mistake, and does not want to proceed, so the program will simply exit if this happens.

We could handle the data source the same way, but the data source is likely to be a considerably longer string of text, and we probably won’t want to change it very often. In order to avoid any risk to important data, we really should set aside a database that will be used solely for testing purposes. We can always import data from other databases into the test database if we want to run tests against specific data. Entering a long path and name every time we run JetTest would be an unnecessarily inconvenient and error-prone process. Instead, I decided to use an XML configuration file.

The Microsoft .NET framework provides classes to support reading configuration data from an XML file called app.config (for Windows Forms and console applications) or Web.config (for ASP.NET applications). The app.config file for JetTest looks like this:

<?xml version="1.0"?>
<configuration>
    <appSettings>
        <!-- 
            Change the 'value' attribute ('C:\Documents and 
            Settings' etc.) to the path and name of your 
            test MDB. 
        -->
        <add key = "DataSource" value = "C:\Documents and Settings\Brendan Reynolds\My Documents\Blog\Test2.mdb">
        </add>
    </appSettings>
</configuration>

The relevant C# code to read the data source from the configuration file looks like this:

    // Edit the path and name in the configuration file
    // (app.config) to point to your test MDB. See the
    // additional comment in the configuration file.
    System.String strDataSource=
        System.Configuration.ConfigurationSettings.
        AppSettings["DataSource"];

You still need to change the path and name in the configuration file (an XML file is just a text file that includes tags that have special meaning to an XML parser, you can edit it in any text editor, such as Notepad) but you only have to do it occasionally (when the location or name of your test database changes) and you don’t have to recompile the code.

I also tidied up the display a bit by separating the colums with tabs instead of commas. A query with a small number of not-too-wide columns now displays quite neatly. A query with a large number of columns or with very wide columns is never going to look good in a console application. For that you really need features that aren’t available in a console application, like grids and scrollbars.

Here’s the complete source code, with some additional comments.

namespace BJR.JetTest
{
    /// 
    /// JetTest is a console application intended to test the use of 
    /// functions in Microsoft Jet queries.
    /// 
    class JetTest
    {
        /// 
        /// The main entry point for the application. Accepts a 
        /// single argument, the name of the saved Microsoft Jet 
        /// query to be tested.
        /// 
        [System.STAThread]
        static void Main(string[] args)
        {
            System.String strQueryName = System.String.Empty;

            // Did the user specify a query name?
            if (args.Length == 0)
            {
                System.String strPrompt = "Please enter the name " +
                    "of the query you wish to test, then press " +
                    "Enter";
                System.Console.WriteLine (strPrompt);
                strQueryName = System.Console.ReadLine().Trim();
            }
            else
                strQueryName = args[0].Trim();
            if (strQueryName.Length == 0)
            {
                System.Console.WriteLine("No query name specified.");
                return;
            }

            // Connect to the database.
            System.Data.OleDb.OleDbConnection odcn = 
                new System.Data.OleDb.OleDbConnection();
            try
            {
                System.String strProvider = 
                    "Microsoft.Jet.OLEDB.4.0";

                // Edit the path and name in the configuration file
                // (app.config) to point to your test MDB. See the
                // additional comment in the configuration file.
                System.String strDataSource = 
                    System.Configuration.ConfigurationSettings.
                    AppSettings["DataSource"];

                // Concatenate the provider and data source to build 
                // the connection string.
                System.String strConnectionString = 
                    "Provider = " + strProvider + "; " + 
                        "Data Source = " + strDataSource;
                odcn.ConnectionString = strConnectionString;
                odcn.Open();
                System.Data.OleDb.OleDbCommand odcm = 
                    new System.Data.OleDb.OleDbCommand();
                odcm.CommandText = strQueryName;
                odcm.CommandType = 
                    System.Data.CommandType.StoredProcedure;
                odcm.Connection = odcn;

                // Retrieve the data.
                System.Data.OleDb.OleDbDataReader oddr = 
                    odcm.ExecuteReader(System.Data.CommandBehavior.
                    CloseConnection);

                // Loop through the records.
                while (oddr.Read())
                {

                    // Loop through the columns.
                    for (System.Int32 intCol = 0; 
                        intCol < oddr.FieldCount; intCol++)
                    {

                        // Display the result.
                        System.Console.Write(oddr.GetValue(intCol).
                            ToString());
                        if (intCol < (oddr.FieldCount - 1))
                        {
                            System.Console.Write("\t");
                        }
                        else
                        {
                            System.Console.WriteLine();
                        }
                    }
                }
                oddr.Close();
                System.Console.Read();
            }
            catch (System.Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                System.Console.Read();
            }
            finally
            {
                odcn.Dispose();
            }
        }
    }
}


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.


Friday, April 23, 2004

 

Conditional Expressions in JET SQL

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

Part Five

In previous posts, in our investigation into which functions can be used in JET SQL when executed outside the Microsoft Access environment, we covered functions with names beginning with “A” and “C”. (There aren’t any with names beginning with “B”). In this post, we’ll examine those with names beginning with “D”. Of these, the Dir and Dir$ functions failed with “undefined function” error messages when tested in a query within the Microsoft Access environment, and the DoEvents function is one of the functions blocked by the new “sandbox” mode in Access 2003. The following SQL statement includes all of the other functions with names beginning with “D”. With the exception of the DDB (Double-Declining Balance) function, these are all date-related functions.

SELECT 
    Date() AS TestDateVar, 
    Date$() AS TestDateString, 
    DateAdd("yyyy",1,[TestDate]) AS TestDateAdd, 
    DateDiff("d",Date(),[TestDate]) AS TestDateDiff, 
    DatePart("m",[TestDate]) AS TestDatePart, 
    DateSerial(Year(Date()),Month(Date()),[TestLong]) AS TestDateSerial, 
    DateValue("22 April 2004") AS TestDateValue, 
    Day([TestDate]) AS TestDay, 
    DDB(1234.56,123.45,5.5,2.75) AS TestDDB 
FROM tblTest;

“TestDate” and “TestLong” are fields in the table “tblTest”. As the names suggest, their data types are Date/Time and Long Integer, respectively. As always in this series, this rather strange query is not intended to return meaningful information or demonstrate 'best practice', its purpose is simply to investigate whether these functions can be used in a query when executed outside of the Microsoft Access environment.

When run through our test C# console application, “JetTest” (see previous posts) the query executed without error, and returned the same results as when opened in datasheet view in Microsoft Access.

So we can now add the following functions to our list of functions that can be used in JET queries executed outside the Microsoft Access environment:

Date, Date$, DateAdd, DateDiff, DatePart, DateSerial, DateValue, Day, and DDB.

If you look closely, you’ll notice that we can add the Year and Month functions, too.

Plans for future posts (as always, in no particular order and in no specific time-frame) include the following …

Explain the syntax and use of the remaining conditional functions, IIf and Switch. Enhance the JetTest utility and perhaps provide VB.NET and/or VBA versions. Explain how to build the utility for those who may not be familiar with VS.NET.

For the next several posts, I’ll alternate between the list of functions and the other subjects. As this post was all about the list of functions, the next post will be devoted to one of the other subjects (probably the JetTest utility), the one after that will return to the list of functions, and so on until we get through the list.


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.


Thursday, April 22, 2004

 

Conditional Expressions in JET SQL

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

Part Four

As the title of this series indicates, the original intention of the series was to explain the use of conditional expressions in JET SQL. We’ve strayed somewhat from that original intention, largely because some of the issues raised appear to be undocumented (or if they are documented, said documentation seems to be remarkably well hidden) necessitating a lot more trial-and-error investigation than I’d originally expected.

In today’s post, I’m going to return to the original subject, and examine one of the conditional functions that can be used in JET queries, both within and outside of the Microsoft Access environment, namely the Choose function.

The Choose function takes a variable number of arguments. The first argument (referred to as “index” in the help files) is a numeric expression. The subsequent arguments (referred to as “choice-1”, “choice-2”, etcetera in the help files) form a list of possible values that the function may return. The value returned is the value of the argument in the position within the list that matches the value of the “index” argument. In other words, if the “index” argument evaluates to the number 1, the function will return the value of the first argument in the list following the “index” argument. If the “index” argument evaluates to the number 2, the value of the second argument in the “choice” list is returned, etcetera.

If the value of the “index” argument is not a whole number, it is rounded to the nearest whole number before being evaluated.

Clear as mud, right? Perhaps an example might help.

The “Northwind” sample database that comes with Microsoft Access includes a table, “Shippers”, with an AutoNumber primary key field (“ShipperID”) and a text field, “CompanyName”, that stores the names of shipping companies. It contains just three records, with the following values:

ShipperIDCompanyName
1Speedy Express
2United Package
3Federal Shipping

The “Orders” table includes a foreign key field, “ShipVia” that contains values from the “ShipperID” field in the “Shippers” table, i.e. the “ShipperID” of the shipping company that shipped that order.

If you look at the “Orders” table in datasheet view in Microsoft Access, you’ll see the name of the shipping company rather than the “ShipperID”, because the field is defined as a “lookup field”. But this is an Access-specific feature and even within Microsoft Access it controls only what is displayed on the screen. What is actually stored in the “Orders” table is the numeric value or the ShipperID field, not the textual company name that you see displayed. If we were to define a query such as “SELECT ShipVia FROM Orders” and display the results of that query outside of Microsoft Access, in a Microsoft Excel worksheet, a Microsoft Word document, or our very own “JetTest” utility, we’d get the numeric “ShipperID” values, not the company names.

One way to get the shipping company names along with the order details is to join the two tables in a query such as the following …

SELECT Orders.OrderID, Orders.OrderDate, Shippers.CompanyName AS Shipper FROM Shippers INNER JOIN Orders ON Shippers.ShipperID = Orders.ShipVia;

In most circumstances, this is likely to be the best and most efficient way to retrieve this information. But as an example of how to use the Choose function, here’s how we can retrieve the same information using the Choose function and without the join …

SELECT OrderID, OrderDate, Choose([ShipVia], "Speedy Express", "United Package", "Federal Shipping") AS Shipper FROM Orders;

If the “ShipVia” field contains the value 1, the Choose function will return the first item in the list of choices, “Speedy Express”. If the “ShipVia” field contains the value 2, the Choose function will return the second item in the list of choices, “United Package”. And if the “ShipVia” field contains the value 3, the Choose function will return the third item in the list of choices, “Federal Shipping”.

If the value of the “index” argument (the “ShipVia” field in the example above) is less than 1 or greater than the number of items in the list of choices, the Choice function returns Null (a special value indicating a missing or unknown value, which displays as an empty field in the Microsoft Access datasheet view). So in our example, if the “ShipVia” field contained a number less than 1 or a number greater than 3, the “Shipper” column in the query would contain Null. If the “ShipVia” field itself contained a Null value, that would cause an error. The “index” argument must evaluate to a numeric value, and Null is not a number. At the expense of introducing a “forward reference” (something that won’t be explained until later) here’s how we could avoid such errors, using the IIf() function …

SELECT OrderID, OrderDate, ShipVia, IIf([ShipVia] Is Null, "N/A", Choose([ShipVia], "Speedy Express", "United Package", "Federal Shipping")) AS Shipper FROM Orders;

While the Choose function returns the value of only one of the “choice” arguments, it evaluates all of them. This is of little consequence when the arguments are simple string literals as in our example, but can have “side effects” when the arguments are more complex expressions, perhaps including calls to other functions. Take the following example, where the “choice” arguments are calls to a custom VBA function ("MyMsgBox") that in turn calls the built-in MsgBox method …

The query …

SELECT OrderID, OrderDate, ShipVia, IIf([ShipVia] Is Null, "N/A", Choose([ShipVia], MyMsgBox("Speedy Express"), MyMsgBox("United Package"), MyMsgBox("Federal Shipping"))) AS Shipper FROM Orders WHERE OrderID = 10248;

The VBA function …

Public Function MyMsgBox(ByVal TheMessage As String) As String

    MsgBox TheMessage
    MyMsgBox = TheMessage

End Function

While this will return the correct value in the query, it will display three message boxes, one for each of the three items in the list of choices. That’s why I added the criteria (“WHERE OrderID = 10248”) to limit the result to just one record – I didn’t feel like clicking through three message boxes for every record in the Orders table!

If you came here looking for information about using conditional expressions in JET SQL, I hope this post goes some way toward making up for the diversions from that subject in previous posts. If, on the other hand, you were actually more interested in some of the other subjects, don’t worry – I’ll be returning to those in subsequent posts.

Future plans include (as always, in no particular order and in no specific time-frame) …

Continue the investigation into which functions are available in JET SQL when used outside the Microsoft Access environment. Explain the syntax and use of the other conditional functions, IIf and Switch. Enhance the JetTest test utility and perhaps produce VB.NET and/or VBA versions. Explain how to build the utility for those who may not be familiar with VS.NET.


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.


Wednesday, April 21, 2004

 

Conditional Expressions in JET SQL

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

Part Three

Previous posts in this series raised the questions “just what functions can be used in JET queries outside of the Microsoft Access environment?” and “what exactly is the relationship between the JET database engine and the VBA expression service?”

I’m still looking for documentation to answer the first question. (The MDAC SDK lead turned out to be a dead end.) In the meantime, I’ll continue with the trial-and-error process I started in Part Two.

The answer to the second question turns out to be, in general terms at least, fairly simple and not terribly exciting. The VBA expression service is a DLL (Dynamic Link Library). Dynamic link libraries are designed to be shared by multiple applications. So just because the VBA expression service happens to have the letters “VBA” in its name doesn’t mean that it can only be used by VBA, or by an application that hosts VBA, such as Microsoft Access. In general (and anthropomorphic) terms, the JET database engine “knows” how to call functions in the DLL.

In Part Two of this series, we started working our way through the alphabetical list of functions in the VBA help file by testing functions with names that begin with the letter “A”. There aren’t any functions in the list with names beginning with the letter “B”, so the next group of functions to look at are those with names that begin with the letter “C”. It’s a big group, because it includes all the data-type conversion functions which, not surprisingly, begin with “C”, presumably for “Convert”. This group also includes the “Choose” function, one of the conditional functions that this series of posts was supposed to be about in the first place, which is just as well, because I’d almost forgotten about that. (Hey, this is a blog, it’s supposed to be spontaneous, OK?)

As with the Array function that we skipped over in Part Two, some of these functions just wouldn’t make much sense in a query, at least not without being combined in complex expressions with other functions, so we’ll leave them aside for now. Here’s a SQL statement for a query that includes all of the “C” functions that seem relevant and testable in a query …

SELECT
    CBool([TestLong]) AS TestCBool, 
    CByte([TestDouble]) AS TestCByte, 
    CCur([TestDouble]) AS TestCCur, 
    CDate([TestDouble]) AS TestCDate, 
    CDbl([TestDate]) AS TestCDbl, 
    Choose([TestDouble], "One", "Two", "Three", "Four", "Five") AS TestChoose, 
    Chr([TestLong]+34) AS TestChr, 
    Chr$([TestLong]+34) AS [TestChr$], 
    ChrW([TestLong]+34) AS TestChrW, 
    CInt([TestDouble]) AS TestCInt, 
    CLng([TestDouble]) AS TestCLng, 
    Cos([TestDouble]) AS TestCos, 
    CSng([TestDouble]) AS TestCSng, 
    CStr([TestDouble]) AS TestCStr, 
    CVar([TestDouble]) AS TestCVar, 
    CVDate([TestDouble]) AS TestCVDate
FROM tblTest
WITH OWNERACCESS OPTION;

As the names imply, "TestLong" is a long integer field, "TestDouble" is a double field, and "TestDate" is a date/time field.

Again, please remember that the purpose of this rather strange query is not to return meaningful results, but simply to determine whether these functions can be used in JET queries when executed outside the Microsoft Access environment.

The functions that I didn’t attempt to test were “CallByName”, “Command”, “Command$”, and “CreateObject”.

The following functions failed with “undefined function” error messages when I attempted to display the result of the query in datasheet view within Microsoft Access: “ChrB”, “ChrB$”, “CurDir”, “CurDir$”, and “CVErr”. I was expecting the “CurDir” and “CurDir$” functions to be blocked by the new JET “sandbox” mode introduced with Access 2003, but I was expecting a different error message. However, that’s an investigation for another day.

The behaviour of the “CDec” function was also unexpected. When I attempted to use it in the query, in the form “CDec([TestDouble])” I received the following error message: “The expression you entered has a function containing the wrong number of arguments.”

The “CDec” function, when used in VBA code, takes one argument. But a little experimentation soon showed that when used in the query, the function expected two arguments. Given two numeric arguments, the function executed without error in the query, but despite experimenting with various argument values, it always returned the value 0. This, too, will have to be an investigation for another day.

I saved this query using the name “qryTestFunctionsC”.

The next step was to modify the C# test code a little, to accept the name of the query to run as a command-line argument, instead of having to edit the code to change the hard-coded query name for every test …

using System;

namespace JetTest
{
    /// 
    /// Summary description for Class1.
    /// 
    Class Class1
    {
        /// 
        /// The main entry point for the application.
        /// 
        [STAThread]
        static void Main(string[] args)
        {
            if (args.Length == 0)
            {
                Console.WriteLine (@"No query specified.");
                Console.WriteLine();
                Console.WriteLine(@"TestJet requires the name of the query you");
                Console.WriteLine(@"wish to run as a command-line argument.");
                Console.WriteLine();
                Console.WriteLine(@"Example: TestJet myquery where 'myquery' is");
                Console.WriteLine(@"the name of the query you wish to run).");
                Console.WriteLine();
                Console.WriteLine(@"Please press Enter to exit, then try again.");
                Console.Read();
                return;
            }
            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 = args[0];
                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();
            }
        }
    }
}

There’s a lot more we could do to make this code more generic and reusable, for example by accepting the path to the MDB as an argument, and accepting an arbitrary SQL statement rather than requiring the name of a saved query. But aside from the fact that all those parameters would make the application too cumbersome as a command-line driven console application, forcing me to change it to a Windows Forms application, there are security implications to consider here. I don’t want to be held responsible if you accidentally run a DELETE query against your production data. As the application stands at the moment, as long as the hard-coded path points to an MDB that you use solely for testing purposes, the application will not have any access (no pun intended) to production databases. And as long as you don’t save any action queries in your test database, the application will not be able to modify any data even in the test database.

I may very well add additional functionality to the test code in the future, but I’m not going to rush into it without adding safeguards, such as checking for keywords like “DELETE”, “INSERT”, “UPDATE”, etcetera in SQL statements.

We can now run the test application (I called it JetTest) from the command prompt and pass the name of the query as a parameter, e.g. “JetTest qryTestFunctionsC”. The code executes the new query without error. The output is pretty ugly, but that’s not the point. Other than a few formatting differences, it’s the same output that was produced by running the query in the Microsoft Access user interface. So we can now add the following functions to our list of functions that can be used in JET queries executed outside of the Microsoft Access environment:

CBool, CByte, CCur, CDate, CDbl, Choose, Chr, Chr$, ChrW, CInt, CLng, Cos, CSng, CStr, CVar, and CVDate.

Future plans for this series include, in no particular order, and in no specific time-frame …

Test more JET functions. Explain the syntax and use of the Choose function. Enhance the JetTest test utility and perhaps produce VB.NET and/or VBA versions. Explain how to build the test app for those who may not be familiar with VS.NET.


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.


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.


Monday, April 19, 2004

 

Conditional Expressions in JET SQL

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

Part One

The short answer to the second form of this question is no, you can’t use If … Then or Select … Case directly in a Microsoft Access query. If your query will only be used within Microsoft Access applications, you can write a VBA (Visual Basic for Applications) function that uses If … Then or Select … Case and call that VBA function from your query. If your query will be used by other applications, you can use the Choose, IIf, or Switch functions, or you can retrieve the data ‘as is’ and manipulate it as necessary in the calling application.

To illustrate this, we need some test data. For the examples that follow, we need an MDB, TEST.MDB, containing a single table, tblTest. The table has just two fields, TestID, an AutoNumber field and the primary key, and TestText, a text field.

We’ll also need a custom VBA function, TestFunc. The function looks like this …

Public Function TestFunc(ByVal lngInput As Long) As String

    If lngInput = 1 Then
        TestFunc = "It's One"
    Else
        TestFunc = "It's not One"
    End If
    
End Function

Now we can start creating our queries. The first query will include only the Choose, IIf, and Switch functions. The SQL view of this query looks like this …

SELECT 
    tblTest.TestID, 
    IIf([TestID]=1,"A","B") AS Expr1, 
    Switch([TestID]=1,"A",[TestID]=2,"B",True,"C") AS Expr2, 
    Choose([TestID],"A","B","C") AS Expr3
FROM tblTest
WITH OWNERACCESS OPTION;

The second query includes the same three functions, but also adds the NZ function, which is defined in the Microsoft Access object library (in other words, it is part of Microsoft Access, not part of VBA or JET) and a call to our custom VBA function. The SQL view of this second query looks like this …

SELECT 
    tblTest.TestID, 
    IIf([TestID]=1,"A","B") AS Expr1, 
    Switch([TestID]=1,"A",[TestID]=2,"B",True,"C") AS Expr2, 
    Choose([TestID],"A","B","C") AS Expr3, 
    NZ(Choose([TestID],"A","B","C"),"D") AS Expr4, 
    TestFunc([TestID]) AS Expr5
FROM tblTest
WITH OWNERACCESS OPTION;

Both of these queries will run without error within our Microsoft Access application. But what happens when we try to use these queries from an application other than Microsoft Access?

For illustration, I’ve used Microsoft Excel, but you will encounter similar results in any other application or development tool, including other VBA-enabled Microsoft Office applications, VB6, or VB.NET or C#.

The Excel document includes two command buttons (on Sheet1) and the following VBA code in Sheet1’s VBA module …

Option Explicit

Public Sub ExecQuery(ByVal strQueryName As String)

    Dim strProvider As String
    Dim strDataSource As String
    Dim strConnectionString As String
    Dim objConnection As ADODB.Connection
    Dim objRecordset As ADODB.Recordset
    
    On Error GoTo ErrorHandler
    strProvider = "Provider = Microsoft.Jet.OLEDB.4.0"
    
    'Change this to the path and file name of the test MDB on your PC.
    strDataSource = "Data Source = C:\Documents and Settings\Brendan Reynolds\My Documents\TEST.MDB"
    
    strConnectionString = strProvider & ";" & strDataSource
    Set objConnection = New ADODB.Connection
    objConnection.ConnectionString = strConnectionString
    objConnection.Open
    Set objRecordset = objConnection.Execute(strQueryName)
    MsgBox objRecordset.Fields(0)
    objRecordset.Close
    objConnection.Close
    
ExitProcedure:
    Exit Sub
    
ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly Or vbInformation
    If Not objRecordset Is Nothing Then
        If objRecordset.State <> adStateClosed Then
            objRecordset.Close
        End If
    End If
    If Not objConnection Is Nothing Then
        If objConnection.State <> ADODB.adStateClosed Then
            objConnection.Close
        End If
    End If
    Resume ExitProcedure
    
End Sub

Private Sub CommandButton1_Click()

    ExecQuery "qryTest1"
    
End Sub

Private Sub CommandButton2_Click()

    ExecQuery "qryTest2"
    
End Sub

The code in the ExecQuery procedure executes the query specified in the strQueryName argument, and displays the value of the first field of the first record in the resulting recordset in a message box. The code in the Click event procedures of the two command buttons calls this procedure, passing the query names qryTest1 and qryTest2, respectively.

Clicking the first command button, which passes the query name qryTest1 to the ExecQuery procedure, results in a message box displaying the value of the TestID field in the first record. But clicking the second command button, which passes the query name qryTest2, results in an error message

What’s going on here?

The ability to use Microsoft Access functions and custom VBA functions in queries is a very powerful feature of Microsoft Access. But it is a feature of Microsoft Access, and, although it may not be immediately apparent, when we execute the query from another application or development tool, we are not using Microsoft Access. We are using the Microsoft JET database engine.

Although, from most points of view, Microsoft Access and the JET database engine are very closely related, they are separate and independent entities. You can use Microsoft Access without using the JET database engine (a Microsoft Access ADP application serving as the front-end to a Microsoft SQL Server or MSDE database) and you can use the JET database engine without using Microsoft Access, as we are doing when we execute our queries from VBA code behind a Microsoft Excel workbook. Because we are using the Microsoft JET database engine, not Microsoft Access, we are limited to features supported by the Microsoft JET database engine, and the ability to use Microsoft Access functions or custom VBA functions is simply not one of those features.

In future articles, we’ll delve a little deeper into the question of just what functions can be used with JET queries executed outside the Microsoft Access environment, and the syntax and behaviour of some of those functions.


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.


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