Texas Tech University.
TTU Home IT Division IT Help Central

Set up and use an ODBC connection to a database.

IT Help Central - Set up and use an ODBC connection to a database

ODBC (Open Database Connectivity) is a standard protocol for programs (such as Microsoft Access) to obtain access to SQL database servers (such as Microsoft SQL Server or Oracle). 

Thus, for a program like Microsoft Access to obtain access to information on a database server, it is necessary to install an ODBC data source.  If you already have ODBC Data Sources and you just want to know how to use them to connect to your database, click CONNECT USING ODBC Data Sources.

To Illustrated Instructions

Setting up an ODBC Data Source

Step 1: First, find the icon labeled Data Sources in the Administrative Tools folder in Control Panel.  (In Windows 98, it will be labeled ODBC Data Sources (32 bit) and will be present in the Control Panel.) 

Step 2: Click on the Data Sources Icon to start the setup process.  This will produce a window where we can add ODBC data sources.  If the System DSN tab is not selected, select it.  To add a data source, click the Add... button.

Step 3: After clicking the Add... button, a list of drivers will be presented which are used to create the data source.  Scroll down until you find the driver for the database to be accessed.  (In this case, SQL Server.)

Step 4: In this case, we'll select SQL Server as the driver to use because we'll be connecting to a SQL Server database.  After selecting SQL Server from the list above, click the Finish button to specify how the connection will be made to the SQL Server database. 

Step 5: In order to make the connection to the database, the Name, Description, and Server name have to be specified.  (NOTE:  These values will be obtained from the database administrator.)  After entering the connection details, click the Next> button to specify how the database server will verify the authenticity of the login ID.

Step 6: Now select the bottom radio button (With SQL Server authentication using...) as the method used to authenticate.  Use the Login ID and Password obtained from the database administrator and then click the Next> button.  This will open a window to allow specification of the default database.

Step 7: Check the checkbox next to "Change the default database to" and select the database as given by the database administrator.  Click the Next> button to open the next window.

Step 8: When the window opens, just accept the defaults and click the Finish button.  This will produce another window with a summary of the settings for the data source.

Step 9: Test the data source by clicking the Test Data Source button.  If everything works as expected, then the results window will appear.

Step 10: When the test has been completed successfully, just click the OK button to close the window.  The ODBC data source has been successfully created.

Connect using ODBC Data Sources

Step 1: Once an ODBC data source has been created, it can be used by other programs (Access, PowerBuilder, etc...) to gain access to database servers.  In Microsoft Access, go to the File menu and select Get External Data and choose Link Tables... from the resulting menu.

Step 2: This action will produce a window which will allow you to choose the database with which to link.  For ODBC database, choose ODBC Databases from the Link window.

Step 3: After choosing ODBC Databases, the Select Data Source window will open with a list of data sources.  Select the Machine Data Source tab to view installed data sources.  Select the data source to be used from this list.

Step 4: When the appropriate data source is selected, click the OK button to connect to the database server.  A login prompt will appear and the login information supplied will be used here.

Step 5: After successfully connecting to the database server (using a correct Login ID and Password), the Link Tables window will appear with a list of the tables available to choose from.  Select the tables to which you have access and click OK to start opening your tables.

Step 6: The selected tables are now available through Microsoft Access.

Illustrated Instructions

If you already have ODBC Data Sources and you just want to know how to use them to connect to your database, click CONNECT USING ODBC Data Sources (with images).

Setting up an ODBC Data Source

Step 1: First, find the icon labeled Data Sources in the Administrative Tools folder in Control Panel.  (In Windows 98, it will be labeled ODBC Data Sources (32 bit) and will be present in the Control Panel.)  Figure 1 shows where the Data Sources icon is located for Windows XP.

Figure 1.  The Data Sources icon for Windows XP.

Step 2: Click on the Data Sources Icon to start the setup process.  This will produce a window where we can add ODBC data sources.  If the System DSN tab is not selected, select it.  To add a data source, click the Add... button in Figure 2 below.

Figure 2.  ODBC Data Source Administrator.

Step 3: After clicking the Add... button in the window above, a list of drivers will be presented (Figure 3) which are used to create the data source.  Scroll down until you find the driver for the database to be accessed.  (In this case, SQL Server.)

Figure 3. The list of drivers to use for the ODBC data source.

Step 4: In this case, we'll select SQL Server as the driver to use because we'll be connecting to a SQL Server database.  After selecting SQL Server from the list above, click the Finish button to specify how the connection will be made to the SQL Server database.  The following window (Figure 4) opens.

Figure 4.  Specify Name, Description, and Server used for the connection.

Step 5: In order to make the connection to the database, the Name, Description, and Server name have to be specified.  (NOTE:  These values will be obtained from the database administrator.)  After entering the connection details, click the Next> button to specify how the database server will verify the authenticity of the login ID (Figure 5).

Figure 5.  Specify the method used by the SQL Server to verify the authenticity of the login ID.

Step 6: In Figure 5 above, select the bottom radio button (With SQL Server authentication using...) as the method used to authenticate.  Use the Login ID and Password obtained from the database administrator and then click the Next> button.  This will open a window to allow specification of the default database (Figure 6).

Figure 6.  Specify the default database.

Step 7: Check the checkbox next to "Change the default database to" and select the database as given by the database administrator.  Click the Next> button to open the next window (Figure 7).

Figure 7.  General configuration settings for the ODBC connection.

Step 8: When the window shown in Figure 7 opens, just accept the defaults and click the Finish button.  This will produce another window with a summary of the settings for the data source (Figure 8).

Figure 8.  Summary of configuration for the new data source to be created.

Step 9: Test the data source by clicking the Test Data Source button.  If everything works as expected, then the following window (Figure 9) will appear.

Figure 9.  Tests of data source completed successfully.

Step 10: When the test has been completed successfully, just click the OK button to close the window.  The ODBC data source has been successfully created.

Connect using ODBC Data Sources

Step 1: Once an ODBC data source has been created, it can be used by other programs (Access, PowerBuilder, etc...) to gain access to database servers.  In Microsoft Access, go to the File menu and select Get External Data and choose Link Tables... from the resulting menu (Figure 10).

Figure 10.  Choose the Link Tables... option from the menu.

Step 2: This action will produce the following window (Figure 11) which will allow you to choose the database with which to link.  For ODBC database, choose ODBC Databases from the Link window as seen below.

Figure 11.  Link window with a list of database types with which to link.

Step 3: After choosing ODBC Databases, the Select Data Source window will open with a list of data sources.  Select the Machine Data Source tab to view installed data sources (Figure 12).  Select the data source to be used from this list.

Figure 12.  List of installed data sources.

Step 4: When the appropriate data source is selected, click the OK button to connect to the database server.  A login prompt will appear and the login information supplied will be used here.

Figure 13.  Login prompt to connect to the database server.

Step 5: After successfully connecting to the database server (using a correct Login ID and Password), the Link Tables window will appear with a list of the tables available to choose from.  Select the tables to which you have access and click OK to start opening your tables.

Figure 14.  List of tables in the Link Tables window.

Step 6: The selected tables are now available through Microsoft Access.