ODBC Connection Creation using DSN and Connection String

What is ODBC? Open Database Connectivity (ODBC) interface is an Application Programming Interface (API) that works as an intermediary between the data sources and applications. Setting up an ODBC connection allows external applications and programming languages to access data in database management systems (DBMS) and cloud data sources via SQL – in our case, we shall provide an example of how to create, set up and check the ODBC connection between MS Access and SQL Server.

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.

What is ODBC Connection?

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).

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

Application Development Tools

Business Intelligence Analytics Software

Business Intelligence & Analytics Software

Database Management Software

Database Management Software

Document Management Software

Document Management Software

Example How to Create ODBC Connection

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:

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:

After successfully downloading and installing the macOS, all what remains is setting up the ODBC driver connection – the DSN with the name DEVART_SQLSERVER is automatically created. It can be modified or removed, depending on your objective. To do this, you need to:

Set Up ODBC Connection String Parameters on Windows

The connection string contains initializing parameters, such as domain, hostname, username, password, authentication key, etc., that are passed from a data provider to the data source: the data provider receives the ODBC connection string parameters as the value of ConnectionString property.
Each driver has its specific connection string options. Then, the data provider parses the parameters of connection strings, ensuring the syntax format is correct and keywords are supported. To set up the connection string for odbc parameters, you need to:
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:
Add a new User DSN/ System DSN data source or Configure an already existing one. Fill down all the required ODBC connection information:
Instead of confirming the changes, click the … button in the bottom left part of the window. It will show you the ODBC connection string parameters. Copy the current connection string to the clipboard.
Using a text editor of your preference, modify the connection string line with connection options of the appropriate ODBC provider syntax and keywords.
Here is an ODBC connection string example, that will connect to the MSSQL data source, shall utilize the Database_3112, with the appropriate credentials for user_135:

How to Check ODBC Connection on Windows

To check the ODBC connection status in Windows you can use the ODBC Data Source Administrator tool corresponding to your application bittness (32-bit or 64-bit):

Locate your previously created DSN, whether it is User DSN or System DSN and click Configure button:
After ensuring all data entered is correct, click the "Test Connection" button:
If everything is correct, you will see a jolly Connection successful message.
If not, there will be an error that specifies the issue:
How to Check ODBC Connection on Linux

How to Check ODBC Connection on Linux

Run the UnixODBC Test Command utility and test the connection via the following command:

$ isql -v DEVART_SQLSERVER

ODBC Drivers

Reliable and simple to use data connectors for ODBC data sources. Compatible with multiple third-party tools.

Discover Other Data Connectivity Solutions