jim_1234567890
asked on
Creating an ODBC connection for Oracle
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("ODBCWorks
Set testdbConnect = testdbWorkspace.OpenConnec
Set testdbRecordset = testdbConnect.OpenRecordse
Range("A1").CopyFromRecord
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.ConnectionSt
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.
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("ODBCWorks
Set testdbConnect = testdbWorkspace.OpenConnec
Set testdbRecordset = testdbConnect.OpenRecordse
Range("A1").CopyFromRecord
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.ConnectionSt
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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?
"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?
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:test8id
"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 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:test8id
"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:
--> 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.
[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
[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
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"