ODBC Overview

ODBC, Open Database Connectivity, is an open standard method of accessing data. The data to be accessed needs an interpreter (driver), which understands the format of the stored data, and a connection manager that determines how the connection needs to be made. All this information is stored in a so-called Data Source Name (DSN).

Data Source simply means where the data is kept. It can be a file, a database on a Database Management System (DBMS) — or even a live data feed. The purpose of a Data Source is to gather all the technical information needed to access the data — the driver name, network address, network software, and so on — into a single place and make the data access transparent to the user.

There are two types of Data Sources: machine Data Sources and file Data Sources. Although both contain similar information about the source of the data, they differ in the way the information is stored. Because of these differences, they are used in somewhat different manners.

Machine Data Sources are stored on the system with a user-defined name. Associated with the Data Source Name is all the information the database driver needs to connect to the Data Source and that the Driver Manager needs to coordinate all the Data Sources and drivers. There are two machine-data-source types: User- and System Data Sources.

File Data Sources are stored in a file and allow connection information to be used repeatedly by a single user or shared among several users. When a File Data Source is used, the Driver Manager makes the connection to the Data Source using the information from a .dsn file. This file can be manipulated like any other text file. A File Data Source does not have a Data Source name, as does a machine Data Source, and it is not registered to any user or system.

Data Sources are generally created by the end-user or technician with a program called the ODBC Administrator.

Depending on the type of Data Source, the information the ODBC Manager uses and needs is stored in different places:

DSN Location
User A User Data Source is typically used on one machine by one user. The information about the data and driver is stored in the machine’s registry under the HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI. Under this key, the available drivers are stored — one subkey per driver. Furthermore, the available User DSN’s are stored under the “ODBC Data Sources” subkey. User DSN’s are typically used in an environment where there is only one user or as a test for ODBC connectivity.
System A System Data Source is typically used on one machine by all users of that machine. The information about the data is stored in the machine’s registry under the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. Here, all information about the defined system Data Sources is stored. The information about installed drivers is stored under the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI registry key. System DSN’s are typically used when there is a need to access data from one machine. This usually is a server type process, like WebApp server
File A File Data Source stores the information about the data, and how to connect to it, in a disk file. File Data Sources can be shared among all users who have access to the file.