[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5818
  • Last Modified:

Creating an ODBC connection for Oracle


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.
1 Solution
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"
------>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

Mark GeerlingsDatabase AdministratorCommented:
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.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

jim_1234567890Author Commented:
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?

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" = ""
--> 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:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now