Using Oracle to create your tables and then linking them into Microsoft Access can be tricky if you don't know what you are doing. This article is to serve as a sort of guide to help you along the way of linking your Oracle Database into Microsoft Access.
Connecting Oracle and Access
Microsoft Access can be connected to the Oracle database. Microsoft Access does not meet the 5 rules of a true database. Oracle, on the other hand, does. Thus, connecting the two to allow Access to be Oracles front end is beneficial to the end user. In Microsoft Access, find the External Data tab and navigate to the more dropdown tab. Next, navigate to the ODBC database. After choosing the proper configurations, one can connect directly between Access and Oracle.
There are disadvantages to using Microsoft Access however. One is that you cannot create new reports without first creating the new specific views in Oracle. Because of this, you would need to have working knowledge or Oracle and the create view and selection statements in order to accomplish this. Most end users would not have this knowledge and would have to look to the technical savvy in order to solve this predicament. Depending upon your requirements for your database, Microsoft Access may be sufficient or Oracle may be the way to go to ensure the data is in a true database.
Creating The ODBC driver
The first thing that you need to do is to create an ODBC driver. The way that you would do this is by clicking on the Start Menu, going to Settings and clicking on the Control Panel in windows. You would then click on the Administrative Tools and then Microsoft ODBC driver. After clicking on that icon it will open a window where there is the option to Add. You will click the add button and then select the Oracle driver. After clicking on the Oracle driver it will ask for Data Source Name where you will put the name that you would like to save it as, then the name of the Server is whatever server you are using, and finally click on OK to create the ODBC driver. At this point you should be able to see the driver you created in the window.
Creating The Links In Microsoft Access
To create the links in Microsoft Access you will first need to open Access. After opening Access you then need to create a new database and call it whatever you want the name to be. After creating the database you need to click on the Table tab. After that click on the New and then Link Table. A window will pop up and you will see a text box that says Type Of Source, scroll to the down until you come to the ODBC Databases. Doing that will give you a dialog box that has two different tabs. You will want to click on the Machine Data source tab and there you will be able to see the ODBC driver that you created earlier. Click on the driver you created and then it will come up with a logon to Oracle box. Enter the Server Name, User Name, and your Password.
If you have done all of this correctly you will see all of the tables that you created in Oracle in the dialog box. To get them into Access all you have to do is select all of the tables that you want to put into Access and click OK. From there you will just need to follow the dialog boxes. If you have done everything correctly you should be able to see all of the tables in Access now and you can click on them to view them.
:= Interaction Between Access and Oracle =:
Once the connection has been established to the Oracle tables, it is possible to retrive, update, and delete records within the various tables. You may choose to create forms through Access, which allow each of these items to occur. Queries can be generated within Access, granting users access to data without having to write the SQL themselves. Reports can also be generated, providing a solid interface for a systems' users.
This interaction does not allow a user to modify the structure of the tables themselves, thus providing a bit of security from inadvertant deletion. It is possible to append tables, potentially causing harm to the data, but the structure itself is protected. The reverse is possible, in that you can alter a table through the DBMS, for instance changing a field size, and simply refreshing the table within Access will bring in the new changes.
Naming Conventions
When linking to the Oracle tables, it is important to ensure any references to items are called by the proper name. In the Oracle Database, you may have an EMPLOYEE table, however when imported into Access, it may be titled HR_East_Coast_EMPLOYEE. While this will not affect changes to data within the table, if a query calls for the EMPLOYEE.EmpID field, incorrect results will be displayed.
Ease of Use
Using Microsoft Access to link to Oracle database tables is a powerful tool to allow users to interact with data. The available features within the Access program, including the various Wizards and VBA support, allow for less technical users to be more effective. The familiar Microsoft Office format also provides some reassurance to users, making it easier for to learn.
At least from Access 2010, you can create reports from an Oracle Database using Access by simply creating queries in Access and using them to generate the report. As long as the tables are properly formed and linked by primary and foreign keys, Access is able to provide a report with all of the information required.
When creating forms, it is best to take care to ensure that information that is required for proper insertion into the oracle database is created and entered properly. To do this, it is recommended that you use combo boxes for fields that are foreign keys, with their source control the table and column that will provide the required information.
There are instances where additional knowledge may be needed by a user to design forms that will meet the requirements of the end user. Many of these are setup during the wizard, but the designer will need to go in and ensure that formats are met for each field in order to have the user see the information in an orderly fashion.
In larger databases, where you may have multiple instances of a foreign key that will be selected, you will need to add DISTINCT immediately following the SELECT command and before the table and column. This will ensure that the user will only see one instance of a given value.