Chapter 37: SQL Server as an Access Companion
1211
The confirmation dialog box includes a Test Data Source button. Clicking this button
verifies the DSN’s ability to connect to the selected SQL Server instance and database.
The test will fail if any of the parameters specified by the DSN Setup Wizard are incorrect
for the selected SQL Server data source.
If the connection fails, it’s likely that the DSN’s authentication mode is incorrect for the
SQL Server instance. You may need to contact a database administrator or a network
administrator for help configuring the DSN’s connection details.
Tip
SQL Server is rather fussy about its name. Prior to SQL Server 2000, SQL Server assumed the same name as its
host computer because only one instance of SQL Server could be installed on a computer. However, beginning
with SQL Server 2000, a single computer can host multiple SQL Server installations, so the name you use to
reference a SQL Server instance is a bit more complex. The syntax used to reference a SQL Server instance is
MyComputer\MySQLServerInstance, where MyComputer is the name of the host computer, and
MySQLServerInstance is the instance you want to reference.
If only a single SQL Server instance is installed on the local computer, you may be able to specify (local) as
the name of the server. Otherwise, you’ll have to provide the name of the computer and the SQL Server
instance name as described in the preceding paragraph.
After creating, configuring, and testing the new DSN, you’ll be returned to the Select Data Source
dialog box (refer to Figure 37.5). The only difference this time is that the DSN Name box is filled
in with the name of the new DSN you built with the DSN Wizard. Keep in mind that a DSN is a
Windows resource and is not owned by Access. Any ODBC-compliant application on the com-
puter can use the DSN you just created. Access makes the creation process easier by incorporating
the DSN Wizard into the Access user interface.The next step is to link to a table in the SQL Server database:- If the Select Data Source dialog box is not open, use the ODBC Databases button on the
External Data tab to open it (see Figure 37.13).
FIGURE 37.13
The new DSN appears in the DSN list in the Select Data Source dialog box.