Access Keys:
Skip to content (Access Key - 0)

Connect to the Data Warehouse from Microsoft Access

Answer

To access the Data Warehouse from MS Access:

Note: We assume that you have Oracle client installed on your Windows.
  1. Make sure that the tnsnames.ora file, usually in C:\Program Files\oracle\ora92\network\Admin, contains an entry for the Data Warehouse's database:
    warehouse =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = warehouse.mit.edu)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = DWRHS))
    )
    

    if not, copy and paste the above to your tnsnames.ora file.

  2. You need to create a DSN (Data Source Name) file in Access for the Data Warehouse. (Since I already have a copy that uses the tnsname entry above, if you have the same tnsname entry, you may download the attached DSN copy (warehouse.dsn) and put it in the directory: "C:\Program Files\Common Files\ODBC\Data Sources" on your Windows.)
  3. In Access, you can then create a database by "Linking Tables" using "External Data". Just in case you are not familiar with this, here's an example:
    1. Open Access
    2. Create a new database
    3. Click on the External Data menu, pull down More, and select ODBC Database.
    4. Pick Link to the data source by creating a linked table on the pop-up "Get External Data - ODBC Database" window. Click OK.
    5. On the "Select Data Source" pop-up window, in the File Data Source tab, select the warehouse.dsn, and click OK.
    6. You will be prompted for your user name and password in the warehouse server. Log on.
    7. Go down the list of tables in the "Import Objects" window that pops up next, and pick the tables in the Data Warehouse that you would like to link to your Access database. For example, "wareuser.employee_directory", "wareuser.cost_collector", etc.
    8. The Data Warehouse tables are then ready to use in the Access database. Save the Access database.

IS&T Contributions

Documentation and information provided by IS&T staff members


Last Modified:

May 05, 2012

Get Help

Request help
from the Help Desk
Report a security incident
to the Security Team
Labels:
c-warehouse c-warehouse Delete
microsoft microsoft Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
Feedback
This product/service is:
Easy to use
Average
Difficult to use

This article is:
Helpful
Inaccurate
Obsolete
Adaptavist Theme Builder (4.2.3) Powered by Atlassian Confluence 3.5.13, the Enterprise Wiki