User Tools

Site Tools


windows:connectdatabaseusingodbcwithoutdsn

How to connect to a database using ODBC driver and not using DSN

Intro

ODBC is still a great tool for connect to a database. However, in some environments it's difficult to create a DSN to configure the acess to the database. Or even worse, it's cumbersome to have a configuration file with the DSN name and the DSN itself configured in the ODBC administrator and –maybe– a tnsnames.ora file with more configuration details of the database.

So, a way to override the use of a DSN and configure it directly in our program is convenient. In this page I will explain how to achieve that.

Step 1: create a ''test.udl'' for the test

We will start by creating an udl file. For the purpose of this tutorial, I will call mine test.udl. You can create such a file by the means of creating an empty text file (test.txt) and renaming it to test.udl. Open it with double click and you will have something like this:

Step 2: open it and pick an OLE DB provider

Move to the “provider” tab and make sure you select the proper driver, that should be “Microsoft OLE DB Provider for ODBC Drivers”:

Step 3: create a new connection string

Click on “Use connection string” and then on “Build…”:

In the window that will appear, select “New”:

Pick the ODBC driver you want to use:

Windows will request you to save this file as a “something.dsn” file. Select a file; it can be deleted afterwards:

After that, some configuration parameters that are specific for the driver will be requested. Because I've the Microsoft for Oracle Driver, the usual screen requesting TNS, username and password is shown. In other drivers, other screens will appear.

You will see that after this step the important information of the connection string is fullfilled:

Fulfill the username, password and click on “Allow saving password”:

Save all the information and close the window.

Step XX: open the udl file with a text editor

After that, open the *.udl file with a text editor. You will see something like that:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Password=YOUR-PASSWORD-HERE;Persist Security Info=True;User ID=YOUR-USERNAME-HERE;Extended Properties="DRIVER={Microsoft ODBC for Oracle};UID=YOUR-USERNAME-HERE;PWD=YOUR-PASSWORD-HERE;SERVER=YOUR-TNS-HERE"

You can use that as a connection string for your ADODB.connection objects and it won't need any DNS for connection.

Step 3: move back to "connection" and create a new connection string

Moving back to the Connection tab, you have to click on “Use connection string”.

windows/connectdatabaseusingodbcwithoutdsn.txt · Last modified: 2022/12/02 22:02 by 127.0.0.1