What is Microsoft Open Database Connection(ODBC)

ODBC is the primary native data access API for applications written in C and C++ for SQL Server. This open data connectivity is being used by various applications and data sources. ODBC interface is widely used in data integration scenarios. In other word, ODBC can also be defined as a protocol that you use to connect an Access database to an external data source such as Microsoft SQL Server. There’s an ODBC driver for majority of data sources available. Many Other languages that can use ODBC include Python, PHP, COBOL, and Perl etc.

The ODBC interface is designed for use with the C programming language. Use of the ODBC interface spans three areas:
SQL statements,
ODBC function calls, and
C programming.

ODBC consists of four components, working together to enable functions. It allows programs to use SQL requests that access databases without knowing the proprietary interfaces to the databases, handles the SQL request and converts it into a request each database system understands.

Components of ODBC:
Application component Processes and calls the ODBC functions and submits the SQL statements;
Driver manager is component that loads drivers for each application;
Driver component handles ODBC function calls, and then submits each SQL request to a data source; and
Data source itself is data being accessed and its database management system.

Following is the Sample SQL Server ODBC Connection String

DRIVER={Devart ODBC Driver for SQL Server};
Data Source=DBMSSQL;
Initial Catalog=myDatabase;
User ID=sa;
Port=1433

Connecting to SQL Server via ODBC Driver

After installing the driver, create a DSN for SQL Server in the ODBC Data Source Administrator.

1. Open the ODBC Data Source Administrator.
   • Type ODBC Data Sources in the Windows 10 search box and choose the ODBC Data Sources application that matches the bitness of your application (32-bit or 64-bit). You can also open ODBC Data Sources from Control Panel > Administrative Tools. Note that before Windows 8, the icon was named Data Sources (ODBC).
     Or, Alternatively, you can run C:\Windows\SysWOW64\odbcad32.exe to create a 32-bit DSN or C:\Windows\System32\odbcad32.exe to create a 64-bit DSN.
2. Select the User DSN or System DSN. Most applications work with any of them, yet some applications require a specific type of DSN.
3. Click Add. The Create New Data Source dialog appears.
4. Select Devart ODBC Driver for SQL Server and click Finish. The driver setup dialog opens.
5. Enter the connection information in the appropriate fields.
6. To test the connectivity, click Test Connection.
7. Click OK to save the DSN.