ODBC - The Microsoft Open Database Connectivity
ODBC interface allows applications to access data in diverse DBMSs through a single interface. The
application must submit queries in standard ODBC format.
The application will be independent of any DBMS from which it accesses data. This works by adding a
software component called ODBC driver, which is an interface between an application and a specific
DBMS.
ODBC supports SQL and non-SQL databases. Although the application always uses SQL to communicate
with ODBC, ODBC will communicate with non-SQL databases in its native language.
DSN - Data Source Name
DSN - "The name that applications use to request a connection to an ODBC data source." In other words,
it is a symbolic name that represents the ODBC connection.
In techspeak, the database is an "ODBC-compatible data source".
It hides the connection details like database name, directory, database driver, UserID, password, etc.
So when making a connection to the ODBC, you don't have to remember the database name, where it
resides, etc.
ODBC Data Source Administrator is a Windows component for managing ODBC data sources. This
application is available in the Windows Control Panel under Administrative Tools.
To launch ODBC Data Source Administrator enter odbcad32 in Run window.
Types of DSNs

· System DSN: This is the DSN used through out the system. All users on the system can see this

DSN and use it (as long as they know the user ID and password).

· User DSN: This is a DSN created for a specific user. Only the user who created the DSN can see this

and use it.
In both these two cases, DSN details (connection parameters) are stored in the system registry.
ODBC Data Source Administrator on System DSN tab shows DSN that are listed under registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
and on User DSN tab DSN from
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources

· File DSN: Instead of storing the DSN details in registry, they are stored in a file. The file is a simple

text file, with a .DSN extension.
In the registry find key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC File DSN
Check the "ODBC File DSN" keys for the value "DefaultDSNDir".
The value of the Data column is the path to the locations where the File DSNs are stored in the file
system.
Shown below is a generic file DSN for an MS Access database.
------------- file.dsn -------
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
ReadOnly=0
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
Document generated by Confluence on Dez 18, 2009 14:21 Page 2
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=MS Access
DriverId=25
While using the file DSN shown above, you need a few more parameters to reuse it. The final connection
string will look like
"filedsn=c:\webdir\file.dsn;DBQ=c:\database\mydb.mdb;UID=admin;PWD=admin;"
where c:\webdir is the directory where file DSN resides.
ODBC Driver
The list of installed ODBC drivers is found under:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers
Information about each of installed drivers is available in ODBCINST.INI hive.
Value named FileUsage serves as a counter. Driver, used by several applications (FileUsage>1),
won't be deleted on application de-installation.
ODBCINST.INI and ODBC.INI files
Info about ODBC drivers and ODBC data sources is also duplicated in files
%windir%\ODBCINST.INI
%windir%\ODBC.INI
respectively. These files were used in old versions of Windows and still remain in XP in some compatibility
reasons.