Connecting to Banner Database with ODBC (How-to)

This article assumes that you have already launched the Campus Desktop through Citrix Workspace and have opened either Microsoft Access or Microsoft Excel. ODBC allows users direct, table-level access to the Banner databases.

See below for information on using the different methods available.

Connecting Using MS Access

1. Go to the ODBC Database

  1. From the ribbon bar in MS Access, select the External Data tab.
  2. Select New Data Source, then From Other Sources, then ODBC Database.

MS Access External Data Tab

2. From the Get External Data - ODBC Database dialog box, select whether to link or import the table/view.

  • Import the source data into a new table in the current database - select this is you want a one-time download (static view) of the table/view.
  • Link to the data source by creating a linked table (preferred) - select this to see updated information from the table/view each time you access it.

3. From the Select Data Source dialog box

  1. Select the Machine Data Source tab, then the Drake ODBC option.

4.  In the Oracle ODBC Driver Connect dialog box, enter the following:

  • Service Name - there are three options:
    • PROD - This is the LIVE (PRODUCTION) BANNER DATABASE.
      • You can also use DUSIS as a Service Name. 
    • TEST - TEST Banner database is recreated with cloned data from PROD each month.
    • TREL - Test RELease Banner database is primarily used for upgrades and patches released by Ellucian, the vendor for Banner.
  • Oracle User Name - generally follows the format: first initial, middle initial, last name
  • Drake Password

5. In the Link Tables dialog box

  1. Select each table/view to highlight it for use, then select OK.

NOTE: Most views used will start with BANINST1, but may start with SATURN or other schemas. If you can't find your needed table/view, please contact ITS.

6. Request to select a unique record identifier

  1. If you get a request to select a unique record identifier, click Cancel to continue.

7. The new table/view will now display on the left side of the application.

Connecting Using MS Excel (Standard Method)

1. From the Ribbon Bar in MS Excel.

  1. Select the Data tab.
  2. Then select Get Data, then From Other Sources, then From ODBC.

2. From ODBC dialog box

  1. Select the Data Source Name Drake ODBC, then select OK.

NOTE: If you wish to access the TEST or TREL Banner databases, select Advanced options, then type either DBQ=TEST or DBQ=TREL in the Connection String field.

3. If asked, enter the following:

  • Oracle User Name - generally follows the format: first initial, middle initial, last name
  • Drake Password

4. From the Navigator dialog box

  1. Select the table/view you wish to access, then select Load.
  • If selecting multiple tables/views, check the Select multiple items check box before selecting tables/views.

NOTE: Most views used will start with BANINST1, but may start with SATURN or other schemas. If you can't find your needed table/view, please contact ITS.

5. If you receive the error, Restricted data type attribute violation

  1. Please use the steps in Connecting to MS Excel (Alternate Method) to access this table/view. Otherwise, the requested table/view should being loading.

 

Use this method if you are receiving errors when using the standard method

1. From the Ribbon Bar in MS Excel:

  1. Select the Data tab.
  2. Select Get Data, then From Other Sources, then From ODBC.

2. In the From ODBC dialog box

  1. Select the Data Source Name Drake ODBC, then select Advanced Options.
  2. Enter the SQL statement to select the proper table/view, then select OK.
  • SQL Statements are generally in the format "Select * [asterisk] from [Schema].[table/view name]" (e.g., "Select * from baninst1.as_du_advisor_major_tbl")

NOTE: Most views used will use BANINST1 as the schema, but may start with SATURN or another schema.If you don't know the schema of the table/view you need, try BANINST1. If you need further assistance with this, please contact ITS.

NOTE: If you wish to access the TEST or TREL Banner databases, type either DBQ=TEST or DBQ=TREL in the Connection String field.

3. If asked, enter the following:

  • Oracle User Name - generally follows the format: first initial, middle initial, last name
  • Drake Password

4. From the next dialog box.

  1. Verify the expected information is displayed for the table/view you wish to access, then select Load.

Connecting to a Banner Database from other applications

Some applications may use the natural Windows interface to connect; if this is the case, use the process below.

1. Enter Service Name

  • There are three options for Service Name:
    • PROD - This is the LIVE (PRODUCTION) BANNER DATABASE.
      • You can also use DUSIS as a Service Name. 
    • TEST - TEST Banner database is recreated with cloned data from PROD each month.
    • TREL - Test RELease Banner database is primarily used for upgrades and patches released by Ellucian, the vendor for Banner.

2. Enter your Oracle user name

  • Generally follows the format: first initial, middle initial, last name

3. Enter your Drake password

Enter ODBC Credentials

Details

Article ID: 100576
Created
Tue 3/10/20 3:37 PM
Modified
Tue 7/25/23 4:00 PM