Skip to main content

Connecting to RAE Student Data Using MS Access

To use MS Access with the student data in the RAE, you will need to:

  • Have a user account established for the RAE
  • Retrieved your login credentials from the Privileged Access Management (PAM) system
  • Install and configure an ODBC driver for MS Access 
MITS Customers

University units that are MITS customers can download and install the Amazon Redshift ODBC 64-bit driver for MS Access from the Software Center.

  1. Use the Windows search field on the taskbar to open the Software Center.
  2. Click the Amazon Redshift ODBC Driver 64-bit icon
  3. Click the Install button to install the driver on your local machine.
Non MITS Customers

Non-MITS customers will need to have their IT support team manually download and install the Redshift ODBC driver, which is available from the AWS website. Choose the correct 64/32 bit version of the driver based on your version of MS Office. In almost all cases, the 64-bit version should be used.

 

Note: Make sure to download and install version 1 of the ODBC driver (at time of writing, most recent 1.x version is 1.5.20). Do NOT install any version of the ODBC driver that is version 2.0 or above. Version 2.0+ doesn’t work correctly with MS-Access.

The following steps to configure the ODBC driver are for users who are not administrators of their machines. This should only need to be done once.

 

Note: You must be connected to the Cisco VPN in order to use RAE data.

 

Connect to the RAE Student Data via the ODBC Data Source

  1. Open an MS Access database

    steps one through seven of process
  2. Click External Data
  3. Click the New Data Source button drop-down
  4. Click From Other Sources
  5. Click ODBC Database
  6. Select 'Link to the data source by creating a linked table'
  7. Click OK

In the Select Data Source window

steps 8 through 10 of process
  1. Click the Machine Data Source tab
  2. Click the New button
    1. A warning message will appear: “You are logged on with non-administrative privileges. System DSNs could not be created or modified”.
  3. Click OK to continue.
  4. Accept the default type of data source (“User Data Source”) and click Next to continue.
  5. Select the Amazon Redshift (x64) driver from the list and click Next to continue.
  6. In the resulting summary window, click Finish to access the Driver Setup window.

In the Driver Setup Window 

Driver setup fields
  1. Enter the following values (but DO NOT CLICK OK yet)
    1. Data Source Name: [Display name for the configured driver, e.g., Redshift_RAE_PRD. This is 
      what you’ll select when you want to link to RAE data in the future]
    2. Server: redshift-prd.rae.osu.edu
    3. Port: 5439
    4. Database: rsprd
    5. Auth Type: Standard
    6. User: [Your IDMxxxxxxx value, copied from PAM]
    7. Password: [The Password value for RedShift PRD IDMxxxxxxx, copied from PAM]
  2. Click Test to verify your connection settings.
    1. A “SUCCESS! Successfully connected to the data source!” message should display.
  3. Click OK to return to the Driver Setup window.
Help Desk /TECH support note

These connections can also be created by opening the ODBC Connection Manager directly (32 or 64-bit based on vesion of MS Office). Either way when you create this connection for a user, make sure to create them as a “User DSN” rather than a “System DSN” while running under the actual user’s account, not your own admin account if possible. The ODBC dialog will display a UAC prompt, but a non-admin account is sufficient to get past the prompt. If you must create the connection as a “System DSN”, then make sure to remove the password from the configuration after testing the connection. Redshift/RAE passwords are auto-rotated every 90 days. If the connection is not created as a “User DSN”, then the user will be unable to update the password when it rotates unless they have admin privileges on their machine.