Oracle ODBC Driver Install

Note: These install instructions are mostly for my own reference for now.

The Oracle ODBC Driver is a software that allows MS Access and other locally installed applications to tap into an Oracle Database.

Preparation

Account to access the schema

A personal account to access the schema is required for every individual accessing the server via ODBC. To have an account set up, email the helpdesk attention to the DBAs. You may need a business reason or authorization. You also need to know which schema you need to access; if the actual schema name is not known, a description such as "the one used by Linda Hathout to access the sleep lab data" may be sufficient.

Driver install files

Needs to be filled in... Ttenbergen 12:04, 28 April 2010 (CDT)

Information needed for install

Database account name and password.

Development Server Data

Service Name: adamdev.wrha

Protocol: TCP/IP

Host Name: 172.16.77.203

Port Number: 1521

Production Server Data

Service Name: adamprod.wrha

Protocol: TCP/IP

Host Name: 172.16.77.202

Port Number: 1521

Install Instructions

1. Copy zip file from Regional Server\ICU\Maintenance\Software\Oracle_V9_Client to C:\ and extract, e.g. to C:\92010NT_CLT for these instructions

Install the Client Software

1. run C:\92010NT_CLT\install\win32\setup.exe to open Oracle Universal Installer
2. click on the "Installed Products" button; the list should be empty. If the list is not empty, you already have Oracle Client software on your system. In that case, the remaining instructions may not work. Abort the install. Find out why Oracle client software is installed and confirm with eHealth that there will be no problems with the other system; suggest that the desktop team should install the client to make sure both applications work afterward.
3. Click the "Next" button
4. Click "Next" again to accept the default source and destination
5. Click the "Custom" radio button, then click "Next"
6. Check the checkboxes next to the following:
   • Oracle Network Utilities (setup utilities)
   • Oracle Database Utilities (setup utilities)
   • Oracle Windows Interfaces (the actual ODBC drivers)
   • Oracle Universal Installer (for future changes to the install)
7. Just go ewith the default for the MTS Server stuff, it should not matter
8. Click "Next" to finish selection, and "Install" on the next screen

Oracle - ODBC setup

For the purposes of this section, the server name should be something like apex.manitoba-ehealth.ca

1. Click on Start Menu --> Programs --> Oracle - OraHome92 --> Configuration and Migration Tools --> Net Manager
2. Click the "+" before Local, and then click on Service Naming
3. Click the green "+" in the panel on the left
4. for Net Service Name, enter the Oracle server name you were given
5. accept the default (TCP/IP) and click Next
6. for Host Name enter the server name again, e.g. apex.manitoba-ehealth.ca
7. click the radio button for (Oracle8i or later) Service Name and enter the server name into the text field the SID or service name you were given, e.g. APEXPRD

After this is done... there is a step to set-up...

Oracle - Orahome 92/configuration.../net manager...

file, open, get the *.ora file that is with the zipped up drivers...

Set Up the ODBC Connection

1. Click on Start Menu --> Programs --> Oracle - OraHome92 --> Configuration and Migration Tools --> Micrsoft ODBC Administrator
2. Click the "Add" button
3. Choose "Oracle in OraHome92" near the bottom of the list and hit "Finish"
4. Enter the following
   • Data Source Name: adamdev
   • Description: <leave empty or say something about CCMDB ODBC Connections>
   • TNS Service Name: adamdev.wrha
   • User ID: <your user ID>
5. Click "Test Connection", enter your password and click OK; you should get a "connection successful" window - if not, check your entries, if all are correct, contact eHealth

Accessing the ODBC data source

Manually in Access

1. In Access, click on File --> Get External Data --> Link Tables...
2. For file type (bottom box) choose "ODBC Databases()" at the bottom of the list
3. Click the "Machine Data Source" tab
4. Click on "adamdev" and then on "OK"
5. Enter your database password
6. Scroll down to the entries starting with "SBOX_CCMDB" and select all entries starting with this; click "OK"

Manually in SAS

<needs to be written>

VBA in Access

<needs to be written>

Contact

The driver software was provided by Peter Piatkowski, DBA with eHealth, after I was unable to find it on the Oracle site without Enterprise grade login. The ODBC drivers must be installed as part of the Oracle V9 Client. The default install does not include the ODBC drivers, so a custom install must be performed.