To access the Data Warehouse from MS Access:
|Note: We assume that you have Oracle client installed on your Windows.|
- 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:
if not, copy and paste the above to your tnsnames.ora file.
- 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.)
- 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:
- Open Access
- Create a new database
- Click on the External Data menu, pull down More, and select ODBC Database.
- Pick Link to the data source by creating a linked table on the pop-up "Get External Data - ODBC Database" window. Click OK.
- On the "Select Data Source" pop-up window, in the File Data Source tab, select the warehouse.dsn, and click OK.
- You will be prompted for your user name and password in the warehouse server. Log on.
- 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.
- The Data Warehouse tables are then ready to use in the Access database. Save the Access database.