Solved

Creating an ODBC connection for Oracle

Posted on 2003-11-26
7
5,495 Views
Last Modified: 2012-08-13
Hello,

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 =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 192.168.11.1)
          (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"
testdbConnect.Open

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.
0
Comment
Question by:jim_1234567890
7 Comments
 
LVL 2

Expert Comment

by:racher
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"
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 155 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

0
 
LVL 34

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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jim_1234567890
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"
"Description"
"Tansport"
"Server"
"Class"
"UserID"
"Attach Statement"

For these options I have put:

"Data Source Name" = "testdb"
"Description" = "testdb"
"Tansport" = "2=tcp/ip"
"Server" = "192.168.11.1:1526"
"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:
"Server"
"Class"
"User Name"
"Password"
"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?





0
 
LVL 23

Expert Comment

by:seazodiac
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" = "192.168.11.1:1526:test8idb"
"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" = ""
0
 

Expert Comment

by:email_rajeevshah
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:
0
 

Expert Comment

by:email_rajeevshah
ID: 25885330
1) I am able to make ODBC/File DSN using followings.
[ODBC]
DRIVER=Microsoft ODBC for Oracle
UID=user1
SERVER=server1
PWD=abc123
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?
 

DSN-Error.bmp
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now