IT Connect

Information technology tools and resources at the UW

Create an ODBC connection

The steps below describe how to create an ODBC connection using Microsoft Access. Before starting, make sure you have opened MS Access with your UW NetId Credentials to connect to EDW data.

Step 1 – Prepare Microsoft Access

  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, choose ‘ODBC Database
    ODBC Database
  3. Select Link to the data source by creating a linked table.
    Link to the data
  4. Select the ‘Machine Data Source’ tab.
  5. If the data source you want is in the list, select it and skip down to Choose Tables. Otherwise, press ‘New’ and continue.
    Machine data source
  6. This warning is for information only. Press ‘OK’ to proceed if you encounter it.
    warning
Step 2 – Create a New Data Source
  1. Select ‘User Data Source (Applies to this machine only), then select Next.
    DataConnection005.jpg
  2. You have to make a choice here. For the best experience with EDW data, we recommend Option 1.
    1. Option 1 (recommended)- Choose “SQL Server Native Client xx.x”, where xx.x is the version number. This driver provides improved functionality (learn more), but requires you to install additional software (download site) in order to take advantage of that functionality. For example, the generic driver (Option 2) does not support Date. The SQL Native Client translates this format in Microsoft Access as a date, while the generic driver translates dates into strings.
      SQL Server by Version
    2. Option 2 – Choose “SQL Server” from the list of drivers. Known issue – driver translates dates into strings in Microsoft Access.
      SQL Server
  3. Verify your selections and select Finish if satisfied.
    DataConnection007.jpg
  4. 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.
    create new data source
  5. Choose ‘With Integrated Windows authentication‘, then select Next
    DataConnection009.jpg
  6. Change the default database to ODS for this example. then select Next.
    DataConnection010.jpg
  7. Choose options as noted below, then select Finish.
    DataConnection011.jpg
  8. To make sure the connection is working, click ‘Test Data Source’.
    DataConnection012.jpg
  9. This is what you should see.
  10. Select OK until you see the Link Table dialog box, then skip to Step 3 – Choose Tables. If you’re using MS Access 2016, continue to the next step.
  11. Selectthe ODBC Database icon again
    ODBC Database
  12. Select Link to the data source by creating a linked table, then select OK
    Link to the data
  13. Select the Machine Data Source tab, then EDW Published ODS, then OK.
    Data Source window

Step 3 – Choose Tables

The list of secured views available from the selected data source are listed on the Link Tables dialog box. Select the view you need and then select OK.

  1. ScreenCapture002.jpg
  2. 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.