Home > Articles

  • Print
  • + Share This
This chapter is from the book

Defining an ODBC Data Source

If you plan to link to ODBC tables, you must load the appropriate ODBC drivers. These drivers come with Access and are installed with the product. You also need to load drivers for the specific back-end database servers to which you want to connect. These drivers usually are purchased from the back-end database vendor and often come with a per-seat charge. This means that you must purchase a client license for each user who will connect to the remote data.

An ODBC data source is a user-defined name that points to a remote source of data. It contains all the properties of the data source that are necessary to communicate with data stored on a database server.

Before you can access a remote table from Access, you must define it by using the ODBC Data Source Administrator. If you do not define that data source, or if it is not defined correctly, you will be unable to access the data.

You set up ODBC data sources in the ODBC Data Source Administrator. (See Figure 3.14.) Depending on your installation, the ODBC Data Source Administrator could be a standalone application, or it could appear as a Control Panel icon. By default, this icon appears as ODBC Data Sources. It enables you to create, modify, and delete data sources and to obtain information about existing drivers. Remember that a data source is simply a user-definable name that stores settings that can be used to access a back end located on a particular server using a specified driver.

Figure 3.14Figure 3.14 The User Data Sources window in the ODBC Data Source Administrator.

The ODBC Data Source Administrator is a tabbed dialog box. Table 3.2 describes how you use these tabs.

Table 3.2 Using the ODBC Data Source Administrator

Tab

Function

User DSN

Enables you to add, delete, and set up data sources that are local to a computer and can be used only by the current user.

System DSN

Enables you to add, delete, and set up data sources that are local to a computer but are not specific to a particular user.

File DSN

Enables you to add, delete, and set up data sources that are file-based and can be shared between all users who have the same drivers installed. File DSNs are not limited to a specific machine.

Drivers

Displays information about installed ODBC drivers.

Tracing

Enables you to specify how the ODBC Driver Manager traces calls to ODBC functions. The available options are when to trace, the log file path, and the custom trace Dynamic Link Library (DLL).

Connection Pooling

Allows you to designate settings that enable you to reuse open connection handles, saving round trips to the server.

About

Gives information about core components such as the location of files and version numbers.


After you enter the ODBC Data Source Administrator, you probably should set up a new data source. To define a new data source, click the Add button on the User DSN or System DSN tabs, or click Add on the File DSN tab of the dialog box. The Create New Data Source dialog box appears, from which you must select the name of the driver that the data source will use. (See Figure 3.15.)

Figure 3.15Figure 3.15 The Create New Data Source dialog box.

The list of available drivers varies, depending on which client drivers have been installed on the machine. After you select a data source and click Finish, a wizard appears, which varies depending on which driver you selected. You use this wizard to define specific information about the data source you are creating. An example is the Create a New Data Source to SQL Server Wizard, shown in Figure 3.16.

  1. The first step of this wizard (shown in Figure 3.16) allows you to specify the data source name, a description for the data source, and the name of the SQL Server you want to connect to. Click Next when you are finished specifying the desired options.

  2. The second step of the Create a New Data Source to SQL Server Wizard appears in Figure 3.17. It allows you to specify login information for the ODBC data source. Select the appropriate choices and click Next.

Figure 3.16Figure 3.16 The first step of the Create a New Data Source to SQL Server Wizard allows you to specify the data source name, a description, and the name of the database server.

Figure 3.17Figure 3.17 In the second step of the wizard, you specify login information for the ODBC data source.

  1. The third step of the wizard, pictured in Figure 3.18, allows you to specify information about the database that you are linking to. In Figure 3.18, the default database is designated as Northwind. Click Next when you are ready to continue.

  2. The fourth and final step of the Create a New Data Source to SQL Server Wizard allows you to further refine the connection to the SQL Server data source (see Figure 3.19). When you're finished specifying all options, click Finish.

Figure 3.18Figure 3.18 The third step of the wizard allows you to specify information for the database you are linking to.

Figure 3.19Figure 3.19 In the fourth step of the wizard, you can specify additional attributes of the data source.

  1. The ODBC Microsoft SQL Server Setup dialog box appears (see Figure 3.20). This dialog box provides you with information about the data source and allows you to test the data source. It is a good idea to test the data source to confirm that all the specified settings are valid. A successful test appears in Figure 3.21. Click OK when you're finished.

NOTE

You might be wondering how you can possibly go through the process of defining data sources on thousands of user machines in a large installation. Fortunately, you can automate the process of defining data sources by using DLL functions. It is a matter of using the ODBC Data Source Administrator DLL function calls to set up the data source by using code. The alternative is to set up file DSNs that are available to all your users.

Figure 3.20Figure 3.20 The ODBC Microsoft SQL Server Setup dialog box provides you with information about the data source and allows you to test the data source.

Figure 3.21Figure 3.21 An example of a successful test of an ODBC data source.

  • + Share This
  • 🔖 Save To Your Account