Connecting InfoAccess to Microsoft Access
Before you start, please make sure you have configured your Oracle ODBC connection with the steps here: Configuring an ODBC connection to InfoAccess in Windows.
- Open Microsoft Access and open the databaes you'd like to use with InfoAccess.
Click "External Data" on the ribbon.
Click on (1) "New Data Source", then (2) "From Other Sources", then (3) "ODBC Database".
A "Get External Data - ODBC Database" wizard will appear. Select "Link to the data source by creating a linked table", and click "OK".
A "Select Data Source" window will pop up. Click on the "Machine Data Source" tab, then on the "InfoAccess" data source name, then click "OK". Note: the data source name will be whatever you called the data source when you set up the system-level ODBC connection for InfoAccess.
An "Oracle ODBC Driver Connect" box will appear with the "Service Name" prefilled. Enter your credentials for InfoAccess and click "OK".
You must now select the tables you wish to add to this Microsoft Access database. Scroll all the way down the list to where the data views start with "UW", and select the views you want. You can click on multiple to add them at the same time. Any highlighted data view will be added, so be judicious with your clicks.
Each data view will generate a "Select Unique Record Identifier" box in which you will have to select the field(s) which are unique among records. See the data documentation for which fields are primary keys. Note: it’s not vital that all primary fields/keys are selected, just do your best.
Once you have completed the unique record identifier for each data view, the tables will appear on the left side under Access Objects.
Now you can create a query using one of the linked tables. To get started, visit lynda.com for excellent Microsoft Access tutorials, or check out these Microsoft Support documents on how to create a simple select query or a make table query.
Need More Information?
If you have questions about this document, please contact the Office of Data Management and Analytics Services (ODMAS, https://data.wisc.edu/) at info@data.wisc.edu.