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.
- From MS Access, open the database in which you want to work – either a new database or an existing one.
- On the External Data tab, open the ‘More’ menu in the Import group.
- Choose ‘ODBC Database’
- Choose to link to the data source. If you choose the import option your data will be stale after the EDW is updated.
- Select the 'Machine Data Source' tab.
- If the data source you want is in the list, select it and skip down to Choose Tables.
- Otherwise, press New and continue.
- This warning may appear. Press ‘OK’ to proceed if you encounter it.
Create a New Data Source
- Select 'User Data Source (Applies to this machine only), then select Next.
- Scroll down to select ‘SQL Server Native Client 11.0’, then press Next.
- Verify your selections and press Finish if satisfied.
- 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.
- Select Next to go to the next window.
- Choose 'With Integrated Windows authentication', then select Next
- Change the default database to ODS for this example. then select Next.
- Choose options as noted below, then select Finish.
- To make sure the connection is working, click ‘Test Data Source’.
This is what you should see.
- Select OK until you see the Link Tables window.
- Scroll through the views and select the ones that you need.
- Choose OK.
- MS Access prompts for 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.