Decision Support

Create an ODBC Connection

Before starting, make sure you have opened Microsoft (MS) Access with your UW NetId Credentials.

The steps below describe how to create an Open Database Connectivity (ODBC) connection between your computer and an Enterprise Data Warehouse (EDW) database using MS Access as the query tool.  After the connection is established, you will be able to link to views in the database, enabling you to start writing queries.

Get Started 

  1. From MS Access, open the database in which you want to work – either a new database or an existing one. 
  2. On the External Data tab, open the ‘More’ menu in the Import group. 
  3. Choose ‘ODBC Database’


  1. Choose to link to the data source.  If you choose the import option your data will be stale after the EDW is updated.

link to the data

  1. Select the 'Machine Data Source' tab. 
  2. If the data source you want is in the list, select it and skip down to Choose Tables.
  3. Otherwise, press New and continue.

    machine data source

  4. This warning may appear.  Press ‘OK’ to proceed if you encounter it.

    warning

Create a New Data Source 

  1. Select 'User Data Source (Applies to this machine only), then select Next.

DataConnection005.jpg

  1. Scroll down to select ‘SQL Server Native Client 11.0’, then press Next.

DataConnection006.jpg

  1. Verify your selections and press Finish if satisfied. 

DataConnection007.jpg

  1. Choose a name and description that describes the data source you want to connect to. This example connects to the database called ODS. Select Next.
  2. Select Next to go to the next window.

create new data source

  1. Choose 'With Integrated Windows authentication', then select Next

DataConnection009.jpg

  1. Change the default database to ODS for this example. then select Next.

DataConnection010.jpg

  1. Choose options as noted below, then select Finish.

DataConnection011.jpg

  1. To make sure the connection is working, click ‘Test Data Source’.

DataConnection012.jpg

This is what you should see. 

  1. Select OK until you see the Link Tables window.

Choose Tables

  1. Scroll through the views and select the ones that you need. 
  2. Choose OK.

    ScreenCapture002.jpg

  3. MS Access prompts for unique key identification. 

    unique key identification

The tables you linked to will now be available in the ‘Tables’ list in MS Access.  You are ready to build queries in MS Access.