Chapter 37: SQL Server as an Access Companion
1215
There are two approaches to sharing SQL Server data at the table level:l Import SQL Server tables into Access. Any changes to the Access copy of the table
won’t be reflected in SQL Server. And any changes to the same tables in SQL Server will
require a refresh in Access, which means a complete re-import of the table. It may be pos-
sible to use VBA code to create an automatic update process, but such a scheme may not
perform well when you’re working with large sets of data.l (^) Link to tables that remain within SQL Server. Linked Access tables can update SQL
Server data because the table and data actually reside in SQL Server, not in Access. In fact,
the interaction between an Access table linked to SQL Server is so seamless that most
users are unaware that they’re working with remote data.
Cross-Reference
Data types and a comparison between Access and SQL Server data types is covered in Chapter 36. Chapter 36
covers upsizing, which means moving tables from Access to SQL Server, and then linking the upsized tables
back to the Access database. This chapter deals with top-level objects, such as tables and views, and not the
structure of those objects (the fields within tables). The real task of this chapter is to show which SQL Server
objects can be accessed from Microsoft Access.
Using SQL Server tables from Access
The Access .accdb database with linked SQL Server tables looks as shown in Figure 37.16.
As shown in Figure 37.17, some tables are local Access tables, and some tables are linked to SQL Server.
The dbo_Employees1 table was imported from the Northwind Traders database on SQL Server. This
table and its data reside entirely in Access; they aren’t connected to SQL Server in any way.
As you can see in Figure 37.17, a new record is being added to dbo_Employees1. The new
record is not present in the SQL Server database because the table resides within Access. Because
the dbo_Employee1 table is not connected to SQL Server, updating the SQL Server table has to
be done using an append query to add the record to the linked dbo_Employees table.
There is no automatic refresh for imported tables in either direction.
Linked data, on the other hand, is (more or less) constantly synchronized between the two data-
bases. Keeping the data synchronized is a rather expensive process. The Access database engine has
to monitor SQL Server for changes, and when the user indicates a need to see the freshest data, the
database engine retrieves the most recent set of data from SQL Server.
Figure 37.18 shows a simple SQL statement in SQL Server Management Studio (the user interface
tool most commonly used to work directly with SQL Server). This statement adds a new record to
the Northwind Employees table. Just below the SQL editor area is a grid showing that the new
record has been added to the Employees table.