UniversalTemplate/logo.png UniversalTemplate/UTS.png

Last updated: 12 March 2015

Setting up an ODBC connection from MS Access to Banner

This document will help you set up your ODBC connection so you can properly exchange information between Banner and Access. Please contact the UTS Helpdesk for any further help with installation.

  1. If you have not yet installed the Oracle client software, follow the instructions in the BannerClientInstallation document first, then return to this document to continue.

  2. The installation of the Oracle client software for Banner creates two system data sources for you by default:
    1. one named "BannerPROD" which points to the production Banner database, and
    2. one for the test Banner database (currently "TEST").
  3. If you already have both of the above data sources and you do not need to define any others, you are done with this procedure; however, if you are missing one or both of the above data sources, or you need a data source for a different database or a data source with a different name from the ones already existing, continue with the following steps.
  4. Click the Start button.
  5. Click Control Panel.
  6. Check the upper-right-hand corner of the Control Panel display. If you are viewing the Control Panel by Large icon or Small icon, click on "Administrative Tools". If you are viewing the Control Panel by Category, click on "System and Security", then click on "Administrative Tools".
  7. Double-click "Data Sources (ODBC)".
  8. Click the "System DSN" tab.
  9. Click the "Add..." button.

  10. Click once on "Oracle in instantclient_11_2" (for Oracle 11g Instant Client) or on "OraClient10g_home1" (for Oracle 10g client) in the Name list. (Please see Figure 1.) Note: If neither of these is available, follow the instructions in the BannerClientInstallation document to install or upgrade your Oracle client software, then return to this document to continue.
    odbcbanner1.jpg

  11. Click Finish
  12. In the "Oracle ODBC Driver Configuration" window, fill in the following fields: (See the illustration below)
    1. Data Source Name: (enter a name for your data source)
    2. Description: (enter a description for your data source)
    3. TNS Service Name: (enter the database name, e.g. PROD, TEST, etc.)
    4. User ID: (put your Banner user ID here)

  13. To prevent ORA-01013 errors, go to the "Application" tab in the "Oracle ODBC Driver Configuration" window, and remove the check mark from the "Enable Query Timeout" field.

    odbcbanner2.jpg

  14. If you want, you may click the "Test Connection" button to verify that your ODBC connection was set up properly. You will be prompted for the connection data, which will be pre-filled for you, except for the password. Enter your password and click OK. If you receive a message saying the test was successful, click OK; otherwise, if you receive an error, please contact the Helpdesk.
  15. Click OK to close the driver configuration window.
  16. Click OK again to close the data sources window.

For further help, please email [email protected] or call (248)370-4357.

DataAdminHowTo

DB_Administration