In the Real WorldSQL as a Second Language

In the Real World SQL as a Second Language

It's tempting to use Jet's Query Design view or the project designer to generate all SQL statements behind the scenes. Graphical QBE bypasses the need to learn two languages SQL and VBA to become a proficient Access or Visual Basic 6.0 database developer. The reality is that you ultimately must master both SQL and VBA, because the two languages are inextricably intertwined in all nontrivial Access database front ends to Jet and SQL Server databases. SQL is the lingua franca of all relational databases, just as VBA is the common programming language of Microsoft Office, Visual Basic 6.0, Visio, and many third-party applications. A future version of Access is likely to accommodate programming in Visual Basic .NET and C#, which means another language or two to learn.

Note

SQL is the foundation for several Microsoft query-language extensions, including SHAPE syntax for generating hierarchical Recordsets and Multidimensional Expressions (MDX) for DataCubes and the PivotTable Service for online analytical processing (OLAP) with Microsoft Analysis Services (formerly OLAP Services and Decision Support Services). As more organizations adopt the Standard or Enterprise editions of SQL Server 2000, which include Analysis Services, data warehouses will become commonplace in medium-sized enterprises, and smaller firms will set up data marts.

If English is your native language, make SQL your second tongue and VBA your third. Access is an exceptionally valuable tool for mastering Jet SQL and T-SQL. If your plans include becoming proficient in client/server database technology, concentrate on ADP and T-SQL.

Choosing Between Views, Stored Procedures, and In-Line Functions

Database administrators (DBAs) traditionally have used views or stored procedures for delivering production data to front-end applications. Recordsets returned by views can be updatable, but updating table data through views isn't a common practice in production environments. Views are restricted to a single SELECT statement and don't accept parameter values to supply WHERE clause and GROUP BY criteria.

Stored procedures can return Recordsets, perform table update operations, or both. Stored procedures can and usually do have parameters to which you can assign default values. If you omit parameter value(s) in the EXECUTE spProcName(param1, param2, ...) statement, the parameter assumes the default value(s). Stored procedures have the additional capability to execute T-SQL procedural code within BEGIN...END blocks and return multiple Recordsets. Recordsets returned by stored procedures aren't updatable.

In-line, table-valued functions combine features of views and stored procedures. These functions return a value of the table data type; thus, you can specify a function name in the FROM clause of an SQL statement. Table values returned by functions are updatable if the function has a single SELECT statement. Functions also accept parameters with optional default values. In this case, however, you must substitute the DEFAULT keyword to use the default values as in SELECT * FROM fnFunctionName(param1, DEFAULT, ...).

Access's project designer hides the T-SQL [CREATE|ALTER] FUNCTION statement required to create or modify an in-line, table-valued function. Here's the statement that creates the example of this chapter's "Implementing Subqueries" section:

 CREATE FUNCTION dbo.fnSubQuery()    RETURNS TABLE    AS RETURN (    SELECT TOP 100 PERCENT ContactName,       CompanyName, ContactTitle, Phone       FROM dbo.Customers    WHERE (CustomerID IN       (SELECT Orders.CustomerID          FROM Orders          WHERE Orders.OrderDate BETWEEN '1/1/1997'             AND '6/30/1997'))    ORDER BY CompanyName) 

The RETURNS TABLE modifier defines the function's data type as table. The AS RETURN statement encloses the SQL statement within parenthesis and delivers the result set. Datasheet view of the function indicates that its table is updatable. You can change values in any of the four columns, but you can't add records because the required CustomerID column is missing from the function's result set.

Microsoft claims that the performance of in-line table-valued functions is about the same as views or stored procedures. Thus, execution speed isn't a deciding factor in the selection process. If you need the equivalent of a Jet parameterized SELECT query, use an in-line function. Otherwise, views and in-line functions offer the same functionality. Stored procedures and dynamic SQL are your best choices for updating tables.

Comparing Dynamic T-SQL with Pre-compiled Query Objects

You can avoid creating pre-compiled views, in-line functions, and stored procedures by executing dynamic (also called ad hoc) T-SQL statements directly. In this case, SQL Server's query optimizer performs the following steps:

1. Parses the T-SQL statement to determine conformance to T-SQL syntax rules and verify the existence of named database objects. Clicking the Verify SQL Syntax button performs this step.

2. Translates named database objects to ID values. For example, table, view, or function names become object IDs, and column names become column IDs.

3. Generates an optimized query execution plan that takes full advantage of table indexes for JOINs and WHERE and GROUP BY criteria.

4. Compiles the optimized query.

5. Executes the compiled query.

Views, functions, and stored procedures execute steps 3 and 4 only when you create or modify them. When you execute the compiled query object, SQL Server's query processor skips steps 3 and 4. Depending on the complexity of your query and its underlying table(s), generating the optimized execution plan and compiling the query can consume a significant amount of server resources.

Fortunately, SQL Server caches the compiled ad hoc query for re-use. Repeated execution of identical ad hoc T-SQL statements also skips steps 3 and 4. In most cases, the query processor recognizes minor changes to WHERE clause and GROUP BY criteria and reuses the cached query.

Making Custom Queries Easy for Users

One of the most common applications for dynamic SQL statements that you create with VBA code is generating the WHERE clause constraints for SELECT query statements. You base the WHERE clause on user selections from one or more drop-down lists. To analyze orders, for example, users make selections in Product, Region, Employee, Start Date, and End Date (dimension) drop-down lists. The query typically returns aggregate values based on the dimensions.

The simplified VBA code for a dynamic T-SQL WHERE clause is

 strWhere = "WHERE Products.ProductID = " & cboProduct.Value & _    " AND Customers.Region = '" & cboRegion.Value & _    "' AND Employees.EmployeeID = '" & cboEmployee.Value & _    "' AND Orders.OrderDate BETWEEN '" & cboStartDate.Value & _    "' AND '" &cboEndDate.Value &"'" 

You populate each list from the appropriate field(s) of a base table, and add an (All) item with a UNION query. When the user selects (All) you eliminate the corresponding WHERE clause constraint, as in:

 strWhere = "WHERE" If cboProduct.Value <> "(All)" Then    strWhere = Products.ProductID = " &cboProduct.Value &_ End If... 

Note

One of the first large-scale client/server applications for Access originally created in version 1.1 used this approach to emulate Analysis Services before the term OLAP was invented. Users selected their dimension values in list boxes, which generated pass-through SQL statements in IBM's DB2 dialect to return aggregated values to temporary Jet tables stored on the client PCs. The temporary tables served as the source for a Jet crosstab query that provided the data source for forms, reports, and graphs.