Sunday, April 29, 2012

MS Access to Oracle ODBC Connection

I recently had an adhoc reporting project using MS Access querying Oracle tables via ODBC. Thought I would share the process for anyone that might need it.

Though I could make the connection read/write, for safety and because I didn't need to make any changes to the Oracle data, a read-only connection was made. Here's the basic overview:

Oracle provides a free local client (Instant Client) and ODBC driver that is used for the connection. It can be downloaded from:

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

It's important to select the correct driver - either 32 or 64 bit. The Oracle documentation doesn't clearly explain but keep in mind that you want the driver version to match your Oracle server version. I used the 32-bit version. After downloading and installing the Oracle "Instant Client" (make sure to download the separate ODBC driver that is part of the Instant Client), you must setup your system PATH and TNS_ADMIN variable as follows:

• In Windows explorer LEFT pane, right click on “My Computer” icon and click “Properties”



• Click “Advanced system settings” tab

• Click Environment variables button:

• In the “System Variables” panel select “Path” variable and click “Edit” button:

• Now find a variable called TNS_ADMIN — it could be in either System or User panel (I chose System so that all users on this PC would have access), if you have no such variable, create it in the User panel by clicking “New”:

• Variable Name: TNS_ADMIN

• Variable Value: C:\InstantClient (or whatever folder you designated in the install above)

• Click Ok

• Now click “Ok” on the Environment Variables screen and click “Ok” on the System Properties window to close it

• Run C:\InstantClient\odbc_install to install the Oracle ODBC driver.

• Run the ODBC Data Source Administrator to configure your connection. Note: The default ODBC
Data Source Administrator is 64-bit so if you are running the 32-bit ODBC driver, you will have to specifically run the above file (on my machine it was found at (C:\Windows\SysWOW64\odbcad32.exe).

• Select “System DSN” and configure as shown:

• Click “Test Connection” to confirm set-up. You will be asked for a TNS Service Name, User ID and password which your Oracle administrator can give you. If all is correct you should see a successful connection message.



Once connected, I was able to design and run all my reports without issue.

0 comments: