Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Creating an ODBC connection for Oracle

Posted on 2003-11-26
Medium Priority
Last Modified: 2012-08-13

I have a base Windows 2000 computer with Oracle installed on it.  Using SQL Plus, Form, or Report I am able to connect to the Oracle Database just fine.

Inside of Excel and Word I wanted to query the Oracle database for some data and return it.  (IE:  in word have a box up where the user can key in a name or id and the macro would return the full address from oracle into the word document or if the data can not be found in oracle prompt the user to fill in the info so it can be inserted into the oracle data base.)

The issue is that ever document I read says just set up an ODBC connection so you can access Oracle.  Others say ‘set up ODBC connection, it is easy’.  But all the docs fail to explain how exactly you can create an ODBC connection (what values to use, etc).  

I go to the Data Sources (ODBC) icon and run the ODBC data source Administrator.  Then the System DNS tab.  Then I click on the Add Button.  Select "Microsoft ODBC for Oracle" as the driver then click Finish.   From there I am prompted for information like "Data Source Name", "Description", "User Name", and "Sever".  I have tried a variety of values for this with no luck.

I have looked at my tnsnames.ora file and the entry for the data I want to connect to.  It looks something like this:

testdb.world =
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host =
          (Port = 1526)
    (CONNECT_DATA = (SID = test8idb)

Although I know that I am not even setting up the ODBC connection correctly I have written a small macro to test the connection.
In a macro for excel or word I want to have code that will access the database and pull information.  So I have code something like this:

sub testODBCconnection ()

 Dim testdbConnect As DAO.Connection
 Dim testdbWorkspace As DAO.Workspace
 Dim testdbRecordset As DAO.Recordset
 Dim sSQL As String

 sSQL = "Select user from dual"

 Set testdbWorkspace = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
 Set testdbConnect = testdbWorkspace.OpenConnection("Header", dbDriverNoPrompt + dbRunAsync, , "ODBC;DSN=test8idb;UID=XXXXX;PWD=XXXXX")
 Set testdbRecordset = testdbConnect.OpenRecordset(sSQL)

 Range("A1").CopyFromRecordset testdbRecordset
 End Sub

Am I making the calls correctly in the macro?

So other docs I have read use:
Dim testdbConnect As ADODB.Connection
Set testdbConnect = New ADODB.Connection
testdbConnect.ConnectionString = "UID=XXXXX;Pwd=XXXXX;DataSource=test8idb;datbase=testdb"

It is not clear when or how to use DAO or ADODB for handling the transaction.

Am I even approaching this correctly to accomplish the task I want?

Most importantly how do I set up the ODBC correctly?  

Any help would be most appreciated.
Question by:jim_1234567890
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 9824996
You said
 From there I am prompted for information like "Data Source Name", "Description", "User Name", and "Sever".  I have tried a variety of values for this with no luck.

What values have you tried?

The way I test odbc to Oracle is to use the Excel: Data Menu : Get External Data : New Database Query
In the list of databases it offers you choose the one that matches the name you have just created in  "Data Source Name"
LVL 23

Accepted Solution

seazodiac earned 465 total points
ID: 9824997
------>I go to the Data Sources (ODBC) icon and run the ODBC data source Administrator.  Then the System DNS tab.  Then I click on the Add Button.  Select "Microsoft ODBC for Oracle" as the driver then click Finish.   From there I am prompted for information like "Data Source Name", "Description", "User Name", and "Sever".  I have tried a variety of values for this with no luck.

Don't Microsoft ODBC for Oracle, instead use Oracle driver, if your oracle is 8i, use Oracle ODBC Driver in the drivers window, if you runs Oracle9i, use ORACLE in ORAHOME (sth to that effect), but you will know it when you see it listed in DRIVERS window.

Then in the SERVER box, you fill in the name as it appears when you connect to oracle using SQLPLUS .

you should be ok using this method.
let me know if this works out for you

LVL 35

Expert Comment

by:Mark Geerlings
ID: 9826102
I agree with seazodiac, use the Oracle driver for ODBC, not the Microsoft one.  When you set that up in the ODBC data sources, I think you will be prompted for four values:
1. Data source name: this can be anything you like, this is what you will see from your ODBC-based app
2. Description: this is optional, anything you like
3. TNS Service name:  this must exactly match an entry in your tnsnames.ora file, so "testdb.world" will work for you.
4. UserID: name of the Oracle user account to log in as

After that, you should have an Oracle ODBC Test utility that you can use.  Lok for a shortcut to it under: Start, Programs, Oracle...,Network Administration.  Connect to the ODBC data source you just set up, then enter a valid select statement in the top window, like:
select sysdate from dual
and click "Execute"
That should get you today's date in your default date format.  If so, your ODBC setup is fine.  If not, something needs to be adjusted.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 9828195
When I get to select data sources all I have in terms of Oracle is one that says:
"Oracle ODBC Driver for Rdb"

When I select that one I am give these options:

"Data Source Name"
"Attach Statement"

For these options I have put:

"Data Source Name" = "testdb"
"Description" = "testdb"
"Tansport" = "2=tcp/ip"
"Server" = ""
"Class" = "generic"
"UserID" = "scott/tiger"
"Attach Statement" = ""

It appears to work to some or at least does not give me any errors when I press 'OK'.  

Or is this "Oracle ODBC Driver for Rdb" not the correct data source to select?  If not how do I find add or select ""Oracle ODBC Driver" when the option is in the list?

I have tried that "Oracle ODBC Test" tool (ODBCTST.EXE).  I click on the connect and have tried to create  a new date source under the File data source.  I have selected "Oracle ODBC Driver for Rdb".  Then save it the file date source and click finish.  Then I am prompted for some values.  Specificly these values are:
"User Name"
"Attach Statement"

I was thinking this was good but...
I tried several different values for each (base upon what information I know from my tnsnames.ora file.  With each attempt I pressed 'OK' and got this error:
"A connection could not be made using the file data source parameters entered.  Save non-verifid file DSN?"

Any more ideas?

LVL 23

Expert Comment

ID: 9828230
everything will be so clear if you just say your oracle database Version.
I think you have option of using either the full oracle database name in the format of "HOST:PORT:SID":

"Data Source Name" = "testdb"
"Description" = "testdb"
"Tansport" = "2=tcp/ip"
"Server" = ""
"Class" = "generic"
"UserID" = "scott/tiger"
"Attach Statement" = ""

or Using service name in your TNSNAMES.ora file

"Data Source Name" = "testdb"
"Description" = "testdb"
"Tansport" = "2=tcp/ip"
"Server" = "testdb.world"
"Class" = "generic"
"UserID" = "scott/tiger"
"Attach Statement" = ""

Expert Comment

ID: 25885195
--> I am able to Login to Oracle SqlPlus at Client side(Oracle).

--> I am able to make ODBC DSN/File DSN and can also connect to Oracle SqlPlus at Server end. I used:

Expert Comment

ID: 25885330
1) I am able to make ODBC/File DSN using followings.
DRIVER=Microsoft ODBC for Oracle
at Server where Oracle(Server) is installed.
and the same way I am able to Login SQL Plus, and able to do various operation using DSN.
2) But at Client end I am able to Login SQL Plus using the same User@Server/Password on Oracle Client.
but I am not able to make ODBC File DSN at Client computer.
and the same way not able to make proper System DSN/User DSN to use for various operation.
Atttmpt to make that ODBC DSN is giving following error.
What can be the cause of this error?
What is Solution or Alternative for this error?


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question