Microsoft Access 2010 Bible

Chapter 37: SQL Server as an Access Companion

1203

Figure 37.2 shows two connection screens for a tool called SQL Server Management Studio (which

can be downloaded from the same site as SQL Server Express). The screen on the left is connecting

to a SQL Server on a remote computer called DAD-PC, using a SQL Server stored user name and

password to authenticate. The screen on the right side of Figure 37.2 is on the local computer,

using the Windows user name to authenticate.

FIGURE 37.2

Connecting to a SQL Server in the Management Studio

SQLCMD is not practical as a user interface to a SQL Server database. It is, instead, a tool for verify-

ing a SQL Server installation, testing login names and passwords, and performing other admin tasks.

Connecting to SQL Server from Access

Creating a connection between Access and SQL Server environments requires a little something

extra, as opposed to just a simple database connection, because both Access and SQL Server are

autonomous environments that must work together. As with many relational databases running

under Windows, drivers are used to allow tools such as SQL Server and Access to communicate.

As is common with many Microsoft software tools and toys, special drivers are created to facilitate

communication between different software products. These drivers can be used to connect tools

such as Excel and Access to an Oracle or DB2 database or, in this case, an Access database con-

nected to SQL Server.

The drivers in question fall into a number of categories and include ODBC, Object Linked

Embedding (OLE), and native drivers. Native drivers are often the best and fastest way to connect

to server database engines, but they tend to be less generic and adaptable, and usually apply to one

specific product or database. Many of these drivers are produced by Microsoft because they all run

under Windows operating systems. Some vendors do produce their own ODBC and OLE drivers,

though.