InfoAccess - Linking MS Access to InfoAccess Database
Instructions for setting up MS Access to query the InfoAccess data base.
Before you are able to have MS Access query an Oracle database such as InfoAccess, you need:
- Connection software for Oracle databases
Check for this by going to Start-->Programs-->Oracle and look to
see if you have Oracle 8 or higher on your PC. If you don't find it, you will
have to install the software by going to Oracle Client Installations at the Oracle website
and following the directions.
- TNSNAMES.ORA file
This file acts as a directory telling your connection software the address of the
database. Go to Start-->Search-->Files and Folders to search
your hard drive for a tnsnames.ora file. Check the entries listed to see if one
of them has a path ending in C:\.....\network\admin. Open this file and search
for Infoaccess. If Infoaccess is not listed in this file please email
InfoAccess for assistance.
Linking Instructions
- Open MS Access 2000/2002/XP. Create a blank database or open an existing database.
- You will either link tables from the InfoAccess database to the MS Access database or you will import the InfoAccess tables into the MS Access database.
Linking Tables
When you link tables, you are essentially using InfoAccess as your back-end. No design changes to the tables will be allowed, but the data can be read and edited. Any data changes will be reflected in the InfoAccess database.
- Go to File-->Get External Data-->Link Tables.
- Using the drop-down menu at the bottom of the "Link" dialogue box, change the "Files of Type" to ODBC Databases ().
- The "Select Data Source" dialogue box immediately comes up. Go to the Machine Data Source tab and select your database, such as UW Data Warehouse or InfoAccess (this is simply a naming preference). Click OK.
- At the "Microsoft ODBC for Oracle Connect" or "Oracle8 ODBC Driver Connect" dialogue box, enter the User Name and Password assigned to you for the InfoAccess database. Use InfoAccess as the "Server". Click OK.
- At the "Link Tables" dialogue box, select the table(s) to link to and click OK.
- Once the tables start linking, a "Select Unique Record Identifier" dialogue box will come up for each table selected. Just click the OK button without selecting fields.
- When finished, the database design window will display the linked tables with an 'arrow and globe' icon indicating they are ODBC linked tables.
Importing Tables
When you import the tables into an MS Access database, you are essentially cutting and pasting the exact tables from InfoAccess into the MS Access database. You will be able to perform design changes on the tables and read and edit the data, but none of the changes will be reflected in the InfoAccess database.
- Go to the File-->Get External Data-->Import.
- Using the drop-down menu at the bottom of the "Import" dialogue box, change the "Files of Type" to ODBC Databases ().
- The "Select Data Source" dialogue box immediately comes up. Go to the Machine Data Source tab and select your database, such as UW Data Warehouse or InfoAccess (this is simply a naming preference). Click OK.
- At the "Microsoft ODBC for Oracle Connect" or "Oracle8 ODBC Driver Connect" dialogue box, enter the User Name and Password assigned to you for the InfoAccess database. Enter Infoaccess as the "Server". Click OK.
- At the "Import Objects" dialogue box, select the table(s) to import and click OK.
- When finished, the database design window will display all of the tables imported from the InfoAccess database.
| Keywords: | MS Access ODBC InfoAccess Linking Tables exporting importing Microsoft databases adding | Doc ID: | 3563 |
|---|
| Owner: | Bonnie C. | Group: | Help Desk |
|---|
| Created: | 2005-03-15 | Updated: | 2008-08-19 |
|---|
