Connecting InfoAccess to Microsoft Access

This document explains how to add an InfoAccess data source 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.

  1. Open Microsoft Access and open the databaes you'd like to use with InfoAccess.
  2. Click "External Data" on the ribbon.

    ExternalData
  3. Click on (1) "New Data Source", then (2) "From Other Sources", then (3) "ODBC Database".

    ODBC database
  4. A "Get External Data - ODBC Database" wizard will appear. Select "Link to the data source by creating a linked table", and click "OK".

    get external data
  5. 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.

    select data source
  6. An "Oracle ODBC Driver Connect" box will appear with the "Service Name" prefilled. Enter your credentials for InfoAccess and click "OK".

    oracle ODBC driver connect
  7. 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.

    link tables
  8. 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.

    select unique record identifier
  9. Once you have completed the unique record identifier for each data view, the tables will appear on the left side under Access Objects.

    import and link
  10. 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 Data, Academic Planning and Institutional Research (DAPIR, https://data.wisc.edu/) at info@data.wisc.edu.



KeywordsInfoAccess Info Access data view table microsoft access add linked shared views   Doc ID89743
OwnerMehrnaz A.GroupData KB
Created2019-02-15 12:13:31Updated2024-05-29 07:37:44
SitesData, Academic Planning & Institutional Research, DoIT Help Desk
CleanURLhttps://kb.wisc.edu/msndata/connecting-infoaccess-data-views-to-microsoft-access
Feedback  1   0