Create an ODBC connection
Before starting, make sure you have opened MS Access with your UW NetId Credentials.
The steps below describe how to create a connection between your computer and an EDW database on a remote server, 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 is from Windows Vista and is for information only. Press ‘OK’ to proceed if you encounter it.
Create a New Data Source
- Press Next
- Scroll down to ‘SQL Server’
- Verify your selections.
- Choose a name and description that describes the data source you want to connect to. This example connects to the database called ODS.
- Click here" for a list of servers. Note the example below uses a port number rather than the server instance name - see the Connecting to Data section at the bottom of this page for more information.
- Click Next to go to the next window.
- Choose Windows NT Authentication.
- Change the default database to ODS for this example.
- 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.