WORKING WITH VIEWS
If you've ever worked with Access queries, you are familiar with the concept of views. In an Access Project, a view is a SQL statement saved to the server that selects, filters, or groups data from one or more tables. Views have various uses in your Project:
They simplify the way users look at data— If your application users are allowed to create their own Access reports, work with data in a datasheet window, or perform ad hoc queries from Excel or a query tool, a view may provide a tool for customizing the columns or rows exposed to various users. For example, you could create different views for different types of users, based on security needs or aptitude. Views are a great way to summarize data into specific subsets without having to teach users how to write SQL. Or, you could create different views as record sources for different forms to simplify application maintenance.
They can be used to transfer or transform data— Sometimes data is moved from one database to another, as in the daily routines that summarize data warehouse information into its related data mart for use by executive query and reporting systems. In other cases, data must be rearranged for download into other products or formats, as in the example of a scheduled event that extracts server data into delimited files for use by Excel or Word. In both scenarios, a view is the best tool for grouping specific columns and records into an appropriate format.
They can help with security— Views are often used to enforce a security model. Frequently, user permissions are removed from server tables so that users can't connect to server tables and edit the data directly. Then, various views are created that provide access to certain rows and columns to specific user groups as their needs require. Security is added to each view to restrict access to the targeted user group. To prevent users from modifying or seeing the SQL statement for a view, you can encrypt a view to lock everyone (including the creator/owner) out of its design.
Note
Access and SQL Server documentation use the terms query and view as mostly interchangeable. For example, the Access help topic “Designing Queries” discusses using the Query Designer to create server views.
When you design a view, bear in mind these strengths and limitations:
A view can't have the same name as a table in the same Project.
You can include tables or other views in a view's definition (in fact, SQL Server is so powerful that you can nest views up to 32 levels deep).
Because views can't contain the Order By clause, they aren't useful for sorting records.
You can use SQL Server expressions and functions only in the view syntax; you can't use any Access features or VBA expressions or functions.
Most views you create that join multiple tables won't be updateable in Access datasheets. You generally have to create a form to update data in a multitable view because the form allows you to specify which index to use to make the view updateable (done via the form's Unique Table property). Views that use Distinct, Group By, Top, or Union can't be made updateable.
Tip
You can add the Distinct keyword to a single table view to change it from updateable to non-updateable if you want the user to have a read-only view of the results.
To create a new view, follow these steps:
1. | |
2. | In the View design window, click the Show Table toolbar button. Drag a table or view from the Show Table dialog to the designer window. |
3. | Select the column(s) to display in the design grid. |
4. | For each column in the grid, set the appropriate attributes, such as Alias and Criteria. You can enter a valid SQL Server expression (such as Employees.FirstName + ' ' + Employees.LastName) in the Column attribute—see the Invoices view in the sample database for examples. |
5. | If you want to view the SQL syntax for a view under construction, click the SQL toolbar button. |
6. | Before saving your view, click the Verify SQL Syntax toolbar button to have the syntax reviewed for flagrant syntax errors. |
7. | Save the view—you must save it before it can be run—and check the results by clicking the Datasheet View toolbar button. |
Figure 25.16 shows a simple view in the designer window.
Figure 25.16. Creating a simple view.
You can fine-tune a view's properties by clicking the Properties toolbar button to open the Properties dialog (see Figure 25.17). The dialog offers these options:
Top— Adds a Top nn clause to the SQL statement, which returns only the first nn rows or first nn percent of the rows in the result set. Enter a number that represents the number of rows or enter the string "nn percent" to return the first nn percent.
Output All Columns— Causes the view to return all columns from all tables in the view.
DISTINCT Values— Groups the data and doesn't display duplicate values in the resulting recordset.
Encrypt View— Locks the view when you close the designer. When encrypted, a view can't be opened in Design view and can't be modified by anyone, even its creator or the database administrator.
Figure 25.17. Setting a view's properties.
To create a multitable view in the view design window, repeat the steps earlier in this section, starting with step 2. If proper relationships exist, the design window will show the default join lines between the tables, and you won't need to do any manual table joins. Because SQL Server views can be complex and can be difficult to debug, you should familiarize yourself with the process of using the Access query designer to create multitable queries before you begin learning how to build SQL Server views.