Microsoft Access 2010 Bible

Part VI: Access as an Enterprise Platform

1218

that may join tables, sort the data, and perform other fundamental operations on the data as it’s

selected. When a client application references the view, SQL Server executes the SQL statements,

producing a table-like dataset of the data.

The result is that an Access query can be executed against a SQL Server view, as if the view were a

table. When the view is accessed in an Access SELECT command, SQL Server executes the view’s

SQL statement against the tables underlying the view and the records returned to the client.

Figure 37.20 shows the creation of a SQL Server view. This view joins the products and categories

tables, but only where the product has not been discontinued.

FIGURE 37.20

Creating a view in SQL Server

Table contents SQL statement Execution results

From Access’s perspective, this view is just another SQL Server table. This view can be linked just

like a native SQL Server table, as shown in Figure 37.21.

In this particular case, during the linking process the product and category names were chosen to

uniquely identify each row. The unique identifier is needed so that, when data in the view is

changed in Access, Access can tell SQL Server which row has been updated. Without a unique

identifier, it would be impossible for Access to reliably update SQL Server data.