A Select query is any query that retrieves data for display by using a SQL SELECT statement.
An action query is a stored procedure that inserts, modifies, or deletes data, by using the SQL INSERT, UPDATE, and DELETE statements.
Storage Views are stored as part of a database design. As you design a database, you can include views in the design for the following reasons:
- Some subsets of data are of interest to many users. Because each view is stored in the database, it establishes a particular subset of data that can be used by any database user.
- Views can conceal base tables. You can disallow all user access to database tables, requiring users to manipulate data through views only. Such an approach can help protect users and application programs from certain database modifications. For example, you can create a view called “Current Month Sales.” On the first of each month, you can modify the view definition accordingly. Without such a view, users would need to rewrite their queries every month to select Sales rows from the appropriate month.
Sorting results You can sort a view, but only if the view includes the TOP clause.
Query plan generation A query plan is an internal strategy by which a database server tries to create result sets quickly. A database server can establish a query plan for a view as soon as the view is saved.
Using Indexed Views and Schema Binding
Indexed views and schema binding
Indexed views are views whose results are persisted in the database and indexed for fast access. Indexed views are supported when your Microsoft Access project is connected to Microsoft SQL Server 2000 Enterprise Edition or SQL Server 2000 Developer Edition, but they are not supported in Microsoft SQL Server 2000 Desktop Edition. Indexed views are best used when data is read-only (such as a decision support system), queries of the indexed view do not involve aggregates or joins, and the base table schema definitions of the indexed view are not likely to change. For more information on indexed views, see the SQL Server documentation.
As with any other views, indexed views depend on base tables for their data. Such dependency means that if you change a base table contributing to an indexed view, the indexed view might become invalid. For example, renaming a column that contributes to a view invalidates the view. To prevent such problems, SQL Server supports creating views with “schema binding.” Schema binding prohibits any table or column modification that would invalidate the view. Any indexed view you create with the Query Designer automatically gets schema binding, because SQL Server requires that indexed views have schema binding. Schema binding does not mean you cannot modify the view; it means you cannot modify the underlying tables or views in ways that would change the view’s result set.
What happens when you modify a base table or column
Using the Table Designer or Database Designer, you might attempt to modify a base table or column that contributes to a view defined with schema binding. If your attempted modification could invalidate the view, the Designer warns you and asks you whether you want to proceed. If you choose to proceed, these things happen:
- Your modifications to the base table occur.
- All views depending on the base table views are changed so that “schema binding” is removed. Thus, your subsequent changes to the base table will proceed without warning.
- If the dependent views were indexed, the indexes are deleted.
The Table Designer and Database Designer warn you before modifying a base table only if that base table contributes to a view with schema binding and if your modification does one or more of the following:
- Deletes the base table
- Renames the base table
- Recreates the base table
- Removes a column from the base table and the view includes that column
- Renames a column from the base table and the view includes that column
Stored procedures can make managing your database and displaying information about that database and its users much easier. Stored procedures are a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit. Stored procedures are stored within a database; can be executed with one call from an application; and allow user-declared variables, conditional execution, and other powerful programming features.
Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values.
You can use stored procedures for any purpose for which you would use SQL statements, with these advantages:
- You can execute a series of SQL statements in a single stored procedure.
- You can reference other stored procedures from within your stored procedure, which can simplify a series of complex statements.
- The stored procedure is compiled on the server when it is created, so it executes faster than individual SQL statements.
A single statement stored procedure contains only one SQL SELECT statement. You can graphically create or edit it in the Query Designer, and you can define extended properties (except column properties).
A multistatement stored procedure contains more than one SQL statement. You can create or edit it in the SQL Text Editor, and you can even graphically edit an SQL SELECT statement in a multistatement stored procedure by using the Query Builder. However, you cannot define extended properties.
When your Microsoft Access project is connected to a Microsoft SQL Server 2000 database, you can create and use user-defined functions. The following example shows the ListCust user-defined function that accepts a named parameter, returns a table, and is used in the FROM clause of a SELECT statement.
SELECT * FROM ListCust(@[Enter a Name])
Overview of user-defined functions
A user-defined function is a query, like a view or stored procedure, that you can:
-
View in the database window.
-
Create in the Query Designer or SQL Text Editor.
-
Use to retrieve data and browse the results in a datasheet
-
Use as the record source of a form, report, or combo box.
Note You cannot use a user-defined function as the record source of a data access page.
-
Programmatically manipulate using the Access object model.
User-defined functions combine the best features of views and stored procedures into a single query that you can nest, pass parameters to, sort, and return values. In many cases, user-defined functions are a better alternative to views and stored procedures because you can return a single table of data or scalar value, hide the business logic and details of how that data or scalar value was created, and simplify the complexity of your SQL statement syntax.
For more information about and examples of user-defined functions, see the SQL Server documentation.
The three types of user-defined functions
Based on what kind of value it returns, each user-defined function falls into one of three categories:
in-line user-defined function Contains a single SELECT statement and returns an updateable table of data. You can use this function in the FROM clause of a query. You can graphically create and edit an inline user-defined function by using the Query Designer. You can also define extended properties.
table user-defined function Contains one or more SELECT statements and returns a non-updateable table of data. You can use this function in the FROM clause of a query. You can create and edit a table user-defined function by using the SQL Text Editor, and you can even graphically edit an SQL SELECT statement in a table user-defined function by using the Query Builder. However, you cannot define extended properties.
scalar user-defined function Contains one or more SELECT statements and returns a scalar value, such as the int, decimal, varchar, sql_variant or table data types. You can use a scalar function in a query anywhere you would use a column name. You can create and edit a scalar user-defined function by using the SQL Text Editor, and you can even graphically edit an SQL SELECT statement in a scalar user-defined function by using the Query Builder. However, you cannot define extended properties.
If you want to repeatedly run the same query, but with different values at different times, you can use a parameter query. A parameter is a placeholder for a value that you type when the query runs. For example, if you want to find all the customers in a Customers table from the same country/region, but prompt for a different country/region each time, you can type @Enter_Country in the Criteria cell of the Design grid in Query Designer and generate the following SQL SELECT statement:
SELECT * FROM Customers
WHERE Country = @Enter_Country
The at sign (@) character in front of the parameter, Enter_Country, tells Access to show the Enter Parameter dialog box so you can type in a country/region name, such as "Mexico" or "Denmark", and use that as the value for the WHERE clause.
You can use parameters as placeholders for either text or numeric literal values. Usually, parameters are used as placeholders in search conditions for individual rows or for groups in the WHERE or HAVING clauses of an SQL statement.
You can even use a combination of parameters to specify a range of dates. For example, you can create two parameters - @Enter_Beginning_Date and @Enter_Ending_Date - in the WHERE clause of a query, and then specify a range of hire dates when you run the query as the following example shows.
SELECT * FROM ORDERS
WHERE ShippedDate
BETWEEN @Enter_Beginning_Date AND @Enter_Ending_Date
You can also use parameters as placeholders in expressions. For example, you might want to calculate discounted prices by supplying a different discount value each time you run a query. To do so, you could enter the expression UnitPrice * @Enter_Discount in the Column cell of the Design grid and generate the following SQL SELECT statement as the following expression shows:
SELECT ProductName, UnitPrice,
(UnitPrice * @Enter_Discount) AS DiscountPrice
FROM Products
Comparing named and unnamed parameters
You can specify two types of parameters, named and unnamed.
Named parameters
Named parameters are easier to read and use, and particularly useful if you have multiple parameters in a query and want to make clear what to enter in each parameter. For example, to prompt and search for an author’s first and last names in an authors table, create the following SQL SELECT statement:
SELECT au_id
FROM authors
WHERE au_fname = @Enter_First_Name AND au_lname = @Enter_Last_Name
When you run the paramter query, Access prompts once for each parameter using the parameter name in the Enter Parameter dialog box so a user is clear which name to enter.
Unnamed parameters
You can also enter the question mark (?) character as an unnamed parameter.
For example, if you want to retrieve all authors from one state in a stored procedure, inline user-defined function, or SQL statement in the record source of a form or report, you can type the question mark (?) character in the Criteria cell of the Design grid in Query Designer and generate the following SQL SELECT statement:
SELECT au_lname, au_fname
FROM state
WHERE state = @Param1
In this case, Access will automatically give the unnamed parameter the generated name, @Param1.
Note Access does not provide a generated name in the case of an SQL Statement in a form or report's record source.
When you can and can't use named parameters
You can use named parameters under the following circumstances:
- When your Microsoft Access project is connected to Microsoft SQL Server database version 2000.
- In the Query Designer of a stored procedure or in-line user-defined function.
- In the SQL Text Editor for a stored procedure, scalar user-defined function, or table user-defined function.
You cannot use named parameters in the SQL statement of a record source that you create in the Query Builder.
You cannot use named or unnamed parameters in a view.
You can create the following types of action queries in the Diagram and Grid panes (the graphical panes) of the Query Designer:
- Append Values query Creates a new row and inserts literal values into specified columns. This type of query creates an SQL INSERT INTO…VALUES statement.
- Append query Creates new rows by copying existing rows from one table into another, or into the same table as new rows. This type of query creates an SQL INSERT…SELECT statement.
- Update query Changes the values of individual columns in one or more existing rows in a table. This type of query creates an SQL UPDATE statement.
- Delete query Removes one or more rows from a table. This type of query creates an SQL DELETE statement.
Note A Delete query removes entire rows from the table. If you want to delete values from individual data columns, use an Update query.
- Make-Table query Creates a new table and creates rows in it by copying the results of a query into it. This type of query creates an SQL SELECT…INTO statement.
You can use a SQL statement as the record source of a form or report or as the row source of a list box or combo box on a form. You might do this to keep the query from being displayed in the database window. You can create the SQL SELECT statement by using the Build
button to the right of the field and graphically design the SQL SELECT statement in the Query Builder, or by editing your own SQL SELECT statement in any text editor and copying and pasting it into the RecordSource property field.