Creating a Data Source to Link to an ODBC Database

Before you can connect to a database that requires ODBC, you must first create a data source-either a data source name (DSN) file or a data source entry in your Windows registry. A data source is simply a named set of ODBC driver parameters that provide the information the driver needs to dynamically link to the data. To create a new data source name file or registry entry, on the External Data tab, in the Import group, begin importing or linking a file by clicking More and then clicking ODBC Database. Access 2007 opens the Get External Data-ODBC Database dialog box. Select either the Import The Source Data Into A New Table In The Current Database option or the Link To The Data Source By Creating A Linked Table option, and then click OK. Click the New button in the Select Data Source dialog box to begin creating a new data source.

You can also create a new data source directly from the Select Data Source dialog box by clicking the New button on either the File Data Source or Machine Data Source tab. If you create a new file data source, Access 2007 stores a file with a .dsn file name extension in your default folder for data source name files. The resulting text file will contain a list of keyword assignment statements to set the values needed by the driver. (You can find an example of a data source name file at the end of this section.) If you create a new machine data source, Access stores the parameters in the Windows registry.

To create a new machine data source, click the Machine Data Source tab and click the New button. Access displays the Create New Data Source wizard, shown here.

To create a data source that applies to all users on your computer, select System Data Source (Applies To This Machine Only) and click Next. Access displays a list of the available ODBC drivers on your system. To create a data source for SQL Server, select SQL Server at the bottom of the list and click Next. Access confirms that you are ready to create a system data source for the driver you specified. Click Finish, and Access displays the Create A New Data Source To SQL Server wizard, shown here.

Enter a name and description for your data source. To connect to the server on your computer, enter your computer name in the Server box. If you are authorized to connect to other servers on your network, click the arrow in the Server box. When you do that, Access 2007 searches your network for other SQL servers and places the names of all servers found in the list. Click Next to go to the next page, shown here.

Depending on how SQL Server 2005 is configured, you might need to enter a login ID and password. By default, SQL Server 2005 uses your Windows logon information (your user name and password) to authenticate you. This means that you don’t have to enter your user name and password a second time when you access the SQL server. If, however, the server is configured to use SQL authentication, you must select With SQL Server Authentication Using A Login ID And Password Entered By The User, and enter your login ID and password. Click Next to see the page where you can specify the default database for this data source, as shown here.

If you are authorized to connect to more than one database on the server and you want to connect to a database other than your default database, select the Change The Default Database To check box. Access logs on to the server and returns a list of available database names. (The preceding figure shows the sample AdventureWorks database that you can install with SQL Server 2005 selected.) If you don’t specify a database name and if multiple databases exist on the server, you’ll be connected to the default database for your login ID. (You don’t need to worry about the other options displayed on this page.) Select the database you want, and click Next.

The last page shows various options, including the ability to change the language of error messages or log data. You can leave these settings as they are and click Finish. Access displays a final confirmation dialog box with a list of the settings you chose. If you need to change anything, click Cancel and then use the Back button in the Create A New Data Source To SQL Server wizard to correct your selections. You can click the Test Data Source button to verify that Access can make a valid connection using your settings. If the test runs successfully, click OK to create your new data source.

Troubleshooting

Why can’t “I connect to my local instance of SQL Server 2005 Express Edition using Windows authentication on my Windows Vista computer?

When you install SQL Server 2005 Express Edition on a system running Windows Vista, the installation process fails to define your Windows logon ID as an authorized user of the system. To correct this problem, you must install Service Pack (2 for SQL Server 2005 Express Edition. At the end of the installation, you’ll be given the chance to define and authorize your logon ID. If you fail to do this, you must uninstall SQL Server 2005 Express Edition, reinstall it with mixed mode authentication, define a system administrator (user ID is “sa”) password, and then use that logon to define and authorize your Windows ID.

If you’re familiar with the parameters required by the driver, you can create your own data source name file. A data source name file like the one listed here for SQL Server begins with the [ODBC] section delimiter and then includes keyword assignment statements for each piece of information the ODBC service needs to correctly load the driver you want. (You can find this file, named SQLServerLocal.dsn, on the companion CD.) Note that you must supply your Windows user ID (your user name) and computer name for YOURID and YOURCOMPUTER, respectively, to connect to the server running on your computer. You can edit any data source name file using a text editor such as Notepad.

 [ODBC] DRIVER=SQL Server UID=YOURID DATABASE=AdventureWorks WSID=YOURCOMPUTER APP=Microsoft® Windows® Operating System Trusted_Connecti on=Yes SERVER=YOURCOMPUTER Description=Sample DSN for SQL Server

The first time you create a data source name file, you’ll probably want to use the Create New Data Source wizard, but after you understand the structure of a valid data source name file for a particular data source, it’s easy to modify an existing file or create a new one.