Microsoft Access 2010 Bible

Chapter 37: SQL Server as an Access Companion

1213

Depending on whether the SQL Server contains a primary key, you may or may not see the dialog

box shown in Figure 37.15. When working with linked tables, Access needs a unique identifier in

the SQL Server table so that updates in Access are properly performed in SQL Server. In the case of

the linked products table in Figure 37.15, the logical choice is to use the ProductID as the

unique identifier. In some cases, you may have to closely examine the data in the SQL Server table

to determine which field, or combination of fields, to use as the unique identifier.

FIGURE 37.15

Selecting a unique identifier in the SQL Server table

Figure 37.16 shows the main Access window with a number of local Access tables and linked SQL

Server tables in the Navigation Pane, and a linked table open in the Access work area to the right

of the Navigation Pane. The globe icon next to the linked tables in Figure 37.15 indicates that SQL

Server is the data source for these files. Other data sources use different icons, so you always know

where the data in your Access applications is hosted. Notice also that the table names are prefixed

with dbo_ in the Access Navigation Pane. (The tables are not renamed in SQL Server, however.)

As you can see, connecting from Access to a SQL Server database is really quite simple, even

though a number of steps are involved. The main advantage of using ODBC to link to SQL Server

tables should be evident from Figure 37.16. Access makes no distinction between a SQL Server

table located on a remote database server and an Access table located in the current database.

Furthermore, there is no reason why an Access database can’t simultaneously connect to multiple

data sources.