How to Create ODBC Connection [Definitive Guide]

What is ODBC Connection?

ODBC API does not depend on any DBMS, operating system (OS) or programming language, though separate drivers are required for accessing each data source. It utilizes the Call-Level Interface specifications (CLI) from Open Group and ISO/IEC for database APIs. As it was previously defined, ODBC tool is a mediator between data sources and applications, and programming languages. It is used by application developers for accessing data in relational, non-relational, and cloud data sources from different vendors.

ODBC connection consists of four components that, in collaboration, connect to data sources and fetch data from them: It functions quite simply: an ODBC-compliant application makes an ODBC call, which therefore is intercepted by an ODBC connection manager that either establishes a connection or communicates the command to the driver, specified in the ODBC connection string or data source. Devart ODBC Driver for SQL Server will serve as an example for this article.

ODBC Driver for Connection

Before attempting an ODBC Driver connection, you have to ensure that you’ve selected an appropriate driver for your needs: each data source has its own set of connection string options, e.g., ODBC Driver for Oracle doesn’t require a security token for a successful connection, whereas ODBC Driver for Salesforce does. If you select an inappropriate driver - it won’t work. Our ODBC Drivers are able to connect to various data sources, including, but not limited to Google BigQuery, Salesforce, Amazon Redshift, SQL Server, Oracle and more. Most drivers are compatible with Windows, macOS, and Linux operating systems, and do not require additional driver configuration, applications or environments.

Popular third-party tools and various IDEs and systems are also supported – you can connect the ODBC Driver to the data source and keep working with your favourite program, be it Microsoft Access, Visual Studio, DBeaver, PowerBI, SSMS or another solution. Devart ODBC drivers are easy to deploy, do not require any additional software such as database clients or vendor-specific libraries and are fully Unicode-compliant (can retrieve and modify data in multilingual databases, regardless of character set).

All sources
Database
Cloud

Compatibility with Third-Party Data Tools

Devart ODBC drivers are verified to be compatible with leading analytics and reporting tools, various IDEs and programming languages. Get fast and secure access to your data in any application using our ODBC Drivers!

Application Development Tools

Business Intelligence & Analytics Software

Database Management Software

Document Management Software

Discover More

Example How to Create ODBC Connection

Windows
Linux
macos

To create an ODBC connection for access database, you need to first install the driver, and then – set up the DSN for SQL Server. To do this on Windows you need to:

Step 1

Launch the ODBC Data Sources Administrator. It can be located via windows start menu search. Please note, that the DSN connection (32-bit and 64-bit) should match the bittnes of the third-party application that will be used. Alternatively, you can directly run C:\Windows\SysWOW64\odbcad32.exe or C:\Windows\System32\odbcad32.exe files accordingly.

Step 2

Select the appropriate tab: User DSN or System DSN. Please note, even though most applications can work with both User DSN and System DSN, some tools do require a specific DSN type.

Step 3

Click the Add button and select the Devart ODBC Driver for SQL Server and confirm selection by clicking the Finish button.

Step 4

Fill down all the required ODBC connection information.

Step 5

Test your connection to ensure everything has been set up properly. In MS Access, go to External Data > ODBC Drivers. You need to specify the way to store data.

Step 6

Select the Machine Data Source as your ODBC data source, choose the database you want to retrieve the data from.

Step 7

After selecting the database, select the tables you will be using: dept and details in our example. Confirm the selection to make MS Access render the data from tables selected.

Creating ODBC connection on Linux is even more straightforward – after either DEB or RPM linux driver package is installed, a DSN with the DEVART_SQLSERVER name is automatically created. To set up the test connection you need to do the following: