Creating an ODBC connection for Oracle
Posted on 2003-11-26
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:
(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)
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.