Jet SQL and T-SQL don't include many of the approximately 200 keywords incorporated in the ANSI standard for SQL-92. Most of the common SQL keywords missing from Jet's implementation are provided by the expressions you create with operators, built-in functions, or user-defined functions you write in Access's VBA flavor. Understanding the similarities and differences between Jet and ANSI-92 SQL is important when you upsize Jet applications to ADP. This knowledge is even more important if you take the "planned migration" route, described near the beginning of the chapter, to upsizing your database front ends. ANSI-92 SQL Reserved Words in Jet SQLAccess doesn't support all the ANSI SQL keywords with identical reserved words in the Jet SQL language, but each update to Jet converges on the SQL-92 standard, as does each successive version of SQL Server. In this chapter, keywords are defined as the commands and functions that make up the vocabulary of the ANSI SQL language. Jet SQL commands and functions are referred to here as reserved words to distinguish them from ANSI SQL. The tables in the following two sections also are intended to acquaint readers who are familiar with ANSI or similar implementations of SQL in other RDBMSs or database front-end applications with the Access implementation of Jet SQL and SQL Server 2000's version of T-SQL. If you haven't used any version of SQL, the tables demonstrate that SQL is a relatively sparse language, having far fewer keywords compared to programming languages such as VBA, and that Jet SQL is even more sparse. Jet SQL has few reserved words to learn. T-SQL implements most ANSI-92 keywords and has many useful additions to ANSI-92 SQL. Jet SQL Reserved Words Corresponding to ANSI SQL and T-SQL KeywordsAccess supports the ANSI SQL and T-SQL keywords listed in Table 22.2 as identical reserved words in Jet SQL. Don't use these reserved words as the names of tables, fields, or variables. The reserved words in Table 22.2 appear in all capital letters in the SQL statements Access creates for you when you design a query or when you add a graph to a form or report. Reserved words marked with an asterisk were introduced by Access 2000 and are accessible only from VBA code and, if you're using ActiveX Data Objects (ADO), require a reference to the Microsoft ADO Ext. 2.x for DDL and Security library (Msadox.dll).
The keywords that relate to data types, CHAR[ACTER], FLOAT, INT[EGER], and REAL, aren't included in Table 22.2 because Jet SQL uses a different reserved word to specify these SQL data types (refer to Table 22.4 later in this chapter). The comparison operators (=, <, <=, >, and =>) are common to both ANSI SQL and Jet SQL. Access and T-SQL substitute the <> operator for ANSI SQL's not-equal (!=) operator. As in ANSI SQL, the IN reserved word in Jet SQL can be used as an operator to specify a list of values to match in a WHERE clause or the result set of a subquery. Jet Functions and Operators Used in Place of ANSI SQL KeywordsTable 22.3 shows reserved words in Jet SQL that correspond to ANSI SQL keywords but are operators or functions used in Jet SQL expressions. Jet doesn't use ANSI SQL syntax for aggregate functions; for example, you can't use the SUM(DISTINCT field_name) or AVG(DISTINCT field_name) syntax of ANSI SQL. Jet distinguishes between its use of the Sum() aggregate function and the SQL implementation, SUM(), by initial letter capitalization. Expressions that use operators such as And and Or are enclosed in parentheses in Jet SQL statements; Jet SQL uses uppercase AND and OR when criteria are added to more than one column.
The Wizard upsizes Jet SQL Like expressions for DateTime values to the ANSI SQL LIKE operator, as mentioned near the beginning of this chapter. For example WHERE DateColumn Like "*/*/1997" upsizes to WHERE DateColumn LIKE '%/%/1997'. (The next section discusses differences between Jet and ANSI SQL wildcard characters.) T-SQL statements containing LIKE constraints for columns of the datetime datatype fail to return rows. If you attempt to edit the LIKE expression, you receive a "Your entry cannot be converted to a valid date time value" message. This use of LIKE is valid in T-SQL; T-SQL statements containing LIKE datetime constraints work fine in the SQL Query Analyzer or from OSQL. The problem with LIKE datetime expressions in the project designer appears to stem from the SQL Server OLE DB provider (SQLOLEDB). The Jet IsNull() function that returns True ( 1) or False (0), depending on whether IsNull()'s argument has a Null value, has no equivalent in ANSI SQL and isn't a substitute for IS NULL or IS NOT NULL qualifiers in WHERE clauses. Jet SQL doesn't support distinct aggregate function references, such as AVG(DISTINCT field_name); the default DISTINCTROW qualifier added to the SELECT statement by Jet serves this purpose. Jet SQL Reserved Words, Operators, and Functions Not in ANSI SQLJet SQL contains a number of reserved words that aren't ANSI SQL keywords (see Table 22.4). Most of these reserved words define Jet data types; some reserved words have equivalents in ANSI SQL and T-SQL, and others don't. You use Jet DDL reserved words to establish or modify the properties of tables and columns. Jet SQL's DISTINCTROW modifier is described in the following section. Jet uses PIVOT and TRANSFORM to create the crosstab queries that are unique to Jet databases.
Jet provides four statistical aggregate functions that aren't incorporated in ANSI SQL. These functions are listed in Table 22.5.
Jet's DISTINCTROW and ANSI SQL's DISTINCT KeywordsJet SQL's DISTINCTROW reserved word that follows the SQL SELECT keyword causes Jet to eliminate duplicated rows from the query's result. The effect of DISTINCTROW is especially dramatic in queries used to display records in tables that have indirect relationships. As mentioned earlier in the chapter, you're likely to encounter DISTINCTROW in Jet queries created prior to Access 97. DISTINCTROW is related to, but not the same as, the DISTINCT keyword in ANSI SQL. Both words eliminate duplicate rows of data in query result tables, but they differ in execution. DISTINCT in ANSI SQL eliminates duplicate rows based only on the values of the data contained in the rows of the query, from left to right. You cannot update values from multiple-table queries that include the keyword DISTINCT. DISTINCTROW eliminates duplicate rows based on the content of the underlying table, regardless of whether additional field(s) that distinguish records in the table are included. DISTINCTROW allows values in special kinds of multiple-table Recordset objects to be updated. Note You can use the Unique Table property value of a form's record source to make most one-to-many queries updatable. Specify the name of the table on the many side of the relationship as the value of the Unique Table property. To distinguish between these two keywords, assume that you have a table with a LastName field and a FirstName field and only 10 records, each with the LastName value, Smith. Each record has a different FirstName value. You create a query that includes the LastName field, but not the FirstName field. DISTINCTROW returns all 10 Smith records because the FirstName values differ in the table. DISTINCT returns one record because the FirstName field that distinguishes the records in the table is absent in the query result table. Versions before Access 97 included the default reserved word DISTINCTROW unless you purposely replaced it with the DISTINCT keyword by using the Query Properties dialog's Unique Values Only option. The Jet Query Properties dialog sets the default value of the Unique Values (DISTINCT) and Unique Rows (DISTINCTROW) properties to No. Don't specify Unique Rows in Jet queries you intend to upsize to ADP. Jet and Corresponding SQL Server Data TypesSQL Server has more data types than those specified by the ANSI-92 standard (refer to Table 22.2). For example, ANSI-92 doesn't require conforming RDBMSs to support Unicode characters. Jet 4.0 and SQL Server both support Unicode data types; SQL Server identifies Unicode data type by an n (for National Character) prefix. The Upsizing Wizard automatically converts Jet data types to corresponding SQL Server 2000 data types. Table 22.6 lists Jet data types, the SQL Server data type to which the Wizard converts the Jet data type, and SQL Server data types that are related to the upsized data type. Parentheses enclose SQL Server property or extended property values that must be set to emulate Jet data types.
Jet doesn't have data types that correspond to SQL Server's bigint, char, nchar, sql_variant, user-defined, varchar, and varbinary data types. VBA Functions That Upsize to SQL Server FunctionsT-SQL has many functions that correspond to VBA functions that you commonly use in Jet queries. Table 22.7 lists the VBA functions that the Upsizing Wizard converts to their T-SQL counterparts. The table doesn't include the SQL functions listed earlier in Tables 22.2 and 22.3. You can safely use these VBA functions in Jet queries that you plant to upsize to SQL Server.
VBA Functions That You Must Manually Convert to Related SQL Server FunctionsThe Upsizing Wizard doesn't automatically convert the VBA functions listed in Table 22.8 into the corresponding T-SQL functions that perform similar or identical operations. In most cases, the reason the Wizard doesn't perform the translation is minor syntax differences. Eliminate, if possible, or minimize use of these functions in Jet queries you plant to upsize.
The Format function is one of the most commonly used VBA functions in Jet queries, and DATENAME only handles one of many possible Format expressions.
For examples of the use of T-SQL DATEPART , DATENAME , DATEDIFF , and GETDATE functions, see "The Better Approach for Dynamic and Static Data Stored Procedures," p. 923. Tip SQL Server 2000 Books Online has the standard syntax for T-SQL functions and examples of their use. Click the Index tab and type the function name in the text box to open the Transact-SQL Reference topic for the function. The "Date and Time Functions" topic has links to all functions that accept or return date or time values. If you haven't downloaded the updated SQL Server 2000 Books Online, you can find it at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp. |