Jet SQL

Access uses Jet SQL. In fact, Access uses Jet SQL every time you run a query. You probably create a query in the Query Design window and then simply execute the query as needed without giving the relationship between that query and Jet SQL much thought. The Query Design window is a graphical interface, and Jet SQL doesn't talk in those terms. Instead, Jet SQL converts your graphically expressed question so that Jet (Access's database engine) can process your query. You're just unaware of the behind-the-scenes processes that occur between the time you execute your query and Jet returns the results.

The truth is, Access relies heavily on Jet SQL for almost all processing. That's why learning Jet SQL is an important part of using Access, whether you're working with a single-user desktop application or creating a data-driven website. In this section, you'll learn how Access uses Jet SQL.

The Query Design window isn't the only place you'll see Jet SQL pop up in your applications. Besides queries, you'll use Jet SQL statements as the data source for a number of objects (forms, reports, and controls) and code. In this section, we'll show you how to actually write and use Jet SQL statements in Access objects and code. If you're familiar with Jet SQL's relationship with Access, you can probably skip this section.

Jet SQL Queries

Open any query in Query Design view, and you'll see the graphical interface that lets you communicate with Jet without knowing its language of choice (Jet SQL). For instance, Figure 9.2 shows the Employee Sales by Country query from Northwind—the sample database that comes with Access— in Query Design view.

Figure 9.2: Query Design view reveals a lot about a query.

Note 

Access Projects don't use queries; they use views and stored procedures in the same way SQL Server does. To learn more about views and stored procedures, see Chapter 8.

From the Query Design window we can learn a lot:

• The query is based on two tables, Employees and Orders, and on one query, Order Subtotals.

• The join line indicates a relationship between the EmployeeID fields in the two tables and the OrderID fields in the Orders table and the Order Subtotals query.

• The 1 and the infinity symbols displayed on the join line between the two tables tell us two things: that the relationship is a one-to-many relationship and that referential integrity is enforced. (Access displays these symbols only when referential integrity is enabled.)

  You can set and view referential integrity properties by clicking the Relationships button on the Database toolbar to open the Relationships window. Then, double-click any join line to enable referential integrity or view the current settings in the Edit Relationships dialog box shown in Figure 9.3.

  
  Figure 9.3: View referential integrity settings in the Edit Relationships dialog box.

• The criteria expression, Between [Beginning Date] And [Ending Date], in the ShippedDate field tells us the query will return records between two dates. In addition, the brackets further indicate that the query is a parameter query. When this query is executed, Access prompts the user for a beginning date and an ending date and then incorporates the user's entries into the expression.

Note 

To learn more about referential integrity, see Chapter 5.

Now, let's examine the Jet SQL equivalent to this query. To do so, click the View button on the Query Design toolbar and choose SQL View.

The Jet SQL statement is fairly complicated and identifies the following:

• The PARAMETER keyword identifies the query as a parameter query.

• The expression

  Between [Beginning Date] And [Ending Date]

  finds all the records between two dates.

• The first part of the SELECT clause identifies the fields the statement will include in the results.

• The FROM clause identifies the data source.

• The INNER JOIN clause

  INNER JOIN (Orders INNER JOIN [Order Subtotals]  ON Orders.OrderID = [Order Subtotals].OrderID)  ON Employees.EmployeeID = Orders.EmployeeID

  relates the Orders table and the Order Subtotals query on the OrderID field and the Employees and Order tables on the EmployeeID.

• The WHERE clause

  WHERE (((Orders.ShippedDate) Between [Beginning Date] And [Ending Date]));

  limits the results to only those records that fall between the two dates entered by the user in response to the parameter prompts when the query is executed.

Tip 

The best way to learn Jet SQL is to use it. Begin by reviewing your queries in SQL View. Then, as you become more familiar with the language, start skipping Query Design View and try to write the SQL statements yourself in SQL View. Almost all the interactive commands you use to interact with Access data via the web will involve a SQL statement, so don't underestimate the importance of understanding Jet SQL.

Using Jet SQL as a Data Source

Most Access objects have a specific data source. For instance, a form or a report is (usually) bound to a particular table or query. Controls also have a data source—a list or combo box might limit you to a list of items that the control captured from a table or query.

When using a table or fixed query as a data source, you simply identify the source by name. When using a Jet SQL statement, enter the appropriate Jet SQL statement as the data source.

A quick example is available in the Northwind Products form. Figure 9.4, shows this form open in Query Design view, and the Properties window is displaying properties for the SupplierID control. This combo box is bound to the SupplierID field in the Suppliers table, as noted in the Control Source property.

Figure 9.4: The SupplierID control uses a Jet SQL statement.

Right away you can see that the Row Source property is a Jet SQL statement. To see the entire statement, select the property and press Shift+F2 to view the property setting in the Zoom dialog box. The complete statement

SELECT DISTINCT Suppliers.SupplierID, Suppliers.CompanyName  FROM Suppliers ORDER BY Suppliers.CompanyName;

retrieves a unique list of values from the SupplierID and CompanyName fields and sorts those entries by the CompanyName field. You can use the form to view, update, and enter new product information. Instead of typing the supplier, the user selects a supplier from the control's drop-down list, as shown in Figure 9.5.

Figure 9.5: The Jet SQL statement populates the control with a unique list of suppliers.

Jet SQL versus Fixed Queries

Many developers use Jet SQL statements to populate forms, reports, and controls. You can do so too, even if you don't know Jet SQL well enough to write the statements yourself. Simply create the appropriate query in the Query Design window. If the setting refers to a fixed query, open that query. Then, open the SQL window, highlight the statement, and press Ctrl+C to copy the SQL statement to the Clipboard. Next, select the appropriate form, report, or control property and press Ctrl+V to paste the statement into the Property field. The fixed query is no longer necessary. You can discard it without saving it, or delete it. Both the fixed query and the Jet SQL statement are optimized, so neither has an edge on performance. Using Jet SQL statements instead of fixed queries has only one real advantage—it removes clutter from the Database window.

Note 

The Upsizing Wizard (the wizard that converts an Access database to SQL Server) creates a stored procedure for each Jet SQL statement it encounters as a data source. If any of your objects use the same statement, the wizard creates a stored procedure for each statement instead of creating only one statement. So, check your newly upsized application for duplicate stored procedures.

Executing Jet SQL in Code

When you execute a query, open a form, or select a control, you execute a Jet SQL statement. Executing a query via code is similar—you use a VBA or ADO (ActiveX Data Objects) command to execute the query. From within an Access application, you can use the following methods to execute a SQL statement:

• The DoCmd object's RunSQL method

• The Database object's Execute method (DAO)

• The Connection object's Execute and Open methods (ADO)

• The Command object's Execute method

Most likely you'll never use the first two methods when connecting to a website, so we'll review just the last two. In addition, most of the scripting languages you'll use to interact with an Access database provide their own versions of the Connection and Command objects. The following is simply a review of the equivalent ADO objects you'll use in Access.

The ADO Connection Object

The Connection object is the top-level object in the ADO hierarchy and represents the connection made to the data source through the OLE DB data provider. You can open a connection via the ADO Connection object in two ways:

• By using the Execute method

• By using the Open method

The Execute method uses the following form:

cnn.Execute validSQLstatement, recordcount, options 

In this expression, cnn represents the Connection object, validSQLstatement is any valid SQL statement, recordcount stores the number of records modified by the action, and options indicates how the provider evaluates validSQLstatement (see Table 9.6).

Table 9.6: EXECUTE METHOD ARGUMENTS

Constant	Description
adCmdUnspecified	Doesn't specify the command type argument.
adCmdText	Evaluates as a textual definition of a command or stored procedure call.
adCmdTable	Evaluates as a table name whose columns are all returned by an internally generated SQL query.
adCmdStoredProc	Evaluates as a stored procedure.
adCmdUnknown	Indicates that the type of command isn't known and is the default.
adCmdFile	Evaluates as a persistently stored recordset.
adCmdTableDirect	Evaluates as a table whose columns are all returned.
adAsyncExecute	Executes asynchronously, which means that ADO returns control to the calling program without waiting for the operation to complete. Don't combine this option with the CommandTypeEnum value adCmdTableDirect.
adAsyncFetch	Retrieves remaining rows after the initial quantity is specified in the CacheSize property asynchronously.
adAsyncFetchNonBlocking	The main thread never blocks while retrieving. If the requested row hasn't been retrieved, the current row automatically moves to the end of the file.
adExecuteNoRecords	Command or stored procedure that doesn't return rows but only inserts data. Use this option only to pass as an optional parameter to the Command or Connection Execute method.
adExecuteStream	Results should be returned as a stream. Only passed as an optional parameter to the Command Execute method.
adExecuteRecord	Command or stored procedure that returns a single row, which should be returned as a Record object.
adOptionUnspecified	Command is unspecified.

Note 

In the accompanying table, we use the term asynchronous processing, which means the controlling program continues to execute code before the processing completes. The alternative is synchronous processing, which means the program temporarily stops until the current process is complete. It then continues by processing the subsequent code.

The following code sets a Connection object's Connection property to the current Access Project and then executes a SQL statement:

Dim cnn As ADODB.Connection  Set cnn = CurrentProject.Connection  strSQL = validSQLstatement  Debug.Print strSQL  cnn.Execute strSQL  Set cnn = Nothing

In this code, validSQLstatement is any valid SQL statement.

Note 

The Execute method creates a read-only recordset with a forward-only cursor. If you need more functionality, use the Connection object's Open method.

The previous example uses the Connection object's Execute method to run a SQL statement after setting the connection to the current project. Use the Connection object's Open and Close method to connect to an external data source using the following syntax:

cnn.Open connectionstring, userid, password, options

Debugging with Debug.Print

The code shown in the Connection Execute method example contains a Debug.Print statement. We recommend you get in the habit of including a Debug.Print statement following any statement that defines a SQL statement to a variable (or almost any kind of variable definition statement for that matter). If the statement returns an error message, you can cut and paste the evaluated statement from the Immediate window to the SQL window for debugging. Specifically, run the evaluated statement as a regular query from the SQL window for more specific error messages. The evaluated statement proves invaluable when the statement includes variables, because the evaluate statement includes the actual value, properly concatenated.

In this example, cnn represents a Connection object, connectionstring identifies the connection, userid identifies the user, password is any assigned password if the application is password-protected, and options specifies synchronous or asynchronous processing. Table 9.7 lists and describes the arguments for connectionstring.

Table 9.7: THE CONNECTIONSTRING ARGUMENTS

Argument	Description
Provider	Specifies the connection's provider for the Access database, which is Microsoft.Jet.OLEDB.4.0
File Name	Specifies a provider-specific file
Remote Provider	Specifies a provider for a client-side connection
Remote Server	Specifies the path name of the server in a client-side connection
URL	Specifies an absolute URL, which generally identifies a file or directory

The following code uses the Open method to establish an external connection to the Northwind sample database:

Dim cnn AS ADODB.Connection  Set cnn = New ADODB.Connection  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " _     & "Data Source=C:\Program Files\MicrosoftOffice\" _   & "Office\Samples\Northwind.mdb;" 

Although this chapter is about Access, Table 9.8 lists other provider strings.

Table 9.8: OLE DB CONNECTION STRING PROVIDERS

Provider (Product)	Provider String
Access	Microsoft.Jet.OLEDB.4.0
SQL Server	SQLOLEDB
Oracle	MSDAORA
ODBC	MSDASQL
Index Server	MSIDXS
ActiveDirectory Service	ADSDSOObject

Tip 

If you're using a Data Source Name (DSN), a connection string is as simple as identifying the DSN name in the form cnn.Open = "DSN=datasourcename", in which datasourcename is the name of the DSN you're using in the connection. Be aware that ADO assumes all defaults in this statement, so any passwords or user name variables must be passed before executing this statement. For more information on just what a DSN is and how to create one, see Chapters 4 and 10.

The ADO Command Object

If there's any chance your Access application will be upsized, use the Command object since it's more compatible with SQL Server. This object is used more frequently in ADP (Access Projects), but both Access and SQL Server Desktop support the Command object. The Command object represents a SQL statement, a stored procedure, or a command that's processed by the data source.

Use the following syntax to create and establish a connection via the Command object:

Dim cmd As ADODB.Command  Set cmd = New ADODB.Command  With cmd   .ActiveConnection = validconnectionstring   .CommandText = CommandTypeConstant   .Execute End With

In this example, validconnectionstring is CurrentProject.Connection or a combination of the arguments listed in Table 9.7. If populating a Recordset object, replace the Execute method with the following statement:

Set rst = .Execute

Note 

For more specific information on the ADO Connection and Command objects, see Chapter 10.

SQL Aggregate Functions

In the earlier section, "Built-in Functions," we looked at a number of functions, including Domain functions that consider an entire set of records. You can use these built-in Domain functions with VBA, but you can't use them in SQL statements. Fortunately, SQL provides a number of equivalents, known as SQL aggregates.

The main difference, besides the environment in which you can use these functions, is that SQL aggregates can't be limited by criteria as Domain functions can. SQL aggregates accept only one argument—a reference to a field or an expression that refers to a field. Remember that the field always refers to the underlying data source, and not to a form, a report, or a control. Table 9.9 lists the SQL aggregate functions.

Table 9.9: SQL AGGREGATE FUNCTIONS

Function	Purpose	Access/VBA Equivalent	TSQL (SQL Server Desktop)
Avg	Returns the average or mean value of a set of values	DAvg()	Avg
Count	Returns the number of non-Null values in a set of values	DCount()	Count
Count(*)	Counts the total number of rows	DCount(*)	Count
Sum	Sums the values in a set of values	DSum()	Sum
Min	Returns the smallest value in a set of values	DMin()	Min
Max	Returns the largest value in a set of values	DMax()	Max
First	Returns the value in the first row of the specified field	NA	NA
Last	Returns the value in the last row of the specified field	NA	NA
StDev	Returns the sample standard deviation for a set of records	DStDev()	StDev
StDevP	Returns population standard deviation for a set of records	DStDevP()	StDevP
Var	Returns sample variance for a set of records	DVar()	Var
VarP	Returns population deviation for a set of records	DVarP()	VarP