Setting up ODBC Connection from Microsoft Access to Oracle

Posted: April 24, 2014 in Uncategorized

A few weeks ago I was asked to troubleshoot a user’s ODBC Connection. They were using Microsoft Access and could not view an updated version of a materialized view on their machine.

I got her connection to work properly but it failed again the following month. So we decided to set up a new connection and get rid of her old connection.

Setting up a new ODBC Connection from Microsoft Access:

There are actually three layers involved here:

  • Oracle
  • ODBC
  • MS-Access

Oracle Layer:

The first thing that is necessary is to set up an Oracle user for the connection.

sql>CREATE USER ACCT_USER PROFILE DEFAULT IDENTIFIED BY ******** DEFAULT TABLESPACE USER_DATA ACCOUNT UNLOCK;
sql>GRANT CREATE SESSION TO ACCT_USER;
sql>GRANT CONNECT TO ACCT_USER;
sql>GRANT ODBC_ROLE TO ACCT_USER; --This role will contain the necessary object privileges to the user working with ODBC.

ODBC Layer:

  • From the user’s PC, navigate to START->Programs->Oracle OraClient**_home folder. Look for Configuration and Migration Tools. Within that navigate to Microsoft ODBC Administrator program.
  • Look for an entry for the Oracle software… if there isn’t one, it needs to be created.

Here, you should see a “User DSN” named ACCT_USER which points to the “Oracle OraClient**_home” folder.

  •  Within the ODBC administrator program, click on the “Configure” button, then click on the “Test Connection” button. The password will be the “ACCT_USER” password used when creating the user.
  • Click OK and it should come back successful. If it is not successful, you will need to troubleshoot the above information to discover the error.

Microsoft Access Layer:

At this point, you will have to decide what it is you are using Microsoft Access for while interfacing with the Oracle Database.

  • For our application the user is accessing a materialized view. So, if this is what you are doing, this example will be helpful. If not, you skip to the end.
  • Within Microsoft Access, open the “Design View” on the underlying table or materialized view.
  • Then, click to the “Database Tools” tab at the top of the screen, and then click on the “Linked Table Manager” button at the top, all within the “Design View” of the table, and check the entry for the table or materialized view.
  • Also, note that next to the table name, you should see “DSN=connection_name”.
  • Click OK once he table is highlighted and checked. If the ODBC dataset name (DSN) is not yet assigned to the table, then a dialog box allowing you to assign it should pop up. This is where the linkage is made between this table and the ODBC dataset name (DSN) which we tested in an earlier step.
  • From here on out, the only thing is to enter the Oracle username, password, and database name, click OK, and then run/test the report.

Questions?

Comments?

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s