ODBC Database for Access

Problem Description : 

Setting up ODBC for Microsoft Access

 Resolution : 

First make sure the person who you are adding access to is Banner Group (you will see p: drive on their computer

Also go to p: drive and oracle folder and run odbc_install.exe

Also check environment variables are set to oracle 11_2

Go to ODBC Data Source Administrator 

Go to System DNS tab and Add Oracle in installclient_11_2

set Banner_ODBC as Data Source Name

and PROD2.BANNER.USG.GA.US as TNS Service Name

use BANINST1 as user ID 

(do not test connectivity, it will not work at this point)

After the Oracle ODBC Driver is set up. 

set up the Microsoft ODBC for Oracle

Set PROD2.BANNER.USG.GA.US as the Data Source Name

set username as the employees banner username

To check go into Microsoft Access and start a blank database, go to External Data and ODBC Database, choose link to the data source by creating a linked table

have the user log in when prompted and make sure you are able to view tables from banner. 

Below are the steps to get it to work on a 64-bit machine

Server 2008R2 64-bit

MS Office 2010 32-bit

Oracle Client 32-bit – Key step…do the Custom install and be sure to select the ODBC option.  I selected several others also but the ODBC component was key to install during initial setup

ODBC Manager – use the Odbcad32.exe under c:\Windows\SysWoW64 folder to setup the DSN

With uninstalling Oracle Client, found the following…

Run the deinstall.bat file under client folder

Delete the c:\app folder after running deinistall.  Had a file oci.dll that would not delete.  Had to stop/disable the Distributed Transaction Coordinator server and restart Yoda for it to not be in use so I could delete.  Google also mentioned stopping the COM+ System Application service and, for some odd reason, the VMTools service on virtual servers.  I did not have to stop these 2 services.  I set the back to Automatic (Delayed) mode after deleting OCI.DLL

Delete the c:\program files\oracle and c:\program files (x86)\oracle folders.  It contains your Oracle Home install locations and the Universal Installer will give an error about the Home already existing even though you’ve deleted the c:\app folder.

 Revision Date : 7/25/2013