Create an MS Access application called OracleLink.  From the File menu drop-down, click on Get External Data and pick the Link Tables... hyperlink.  This opens up the following window Link as shown in Figure 2.

Figure 2

Click on the drop-down item ODBC Databases () corresponding to the label Files of Type:, as shown in the above figure.  This opens up the following window, Select Data Source as shown in Figure 3.

Figure 3

.

Here you will create a new File DSN with the extension *.dsn, which allows you to connect to the Oracle XE.  There is already a file called OnConnectedmachine.dsn which will not be used.  Click on the button New… and this opens up the Create New Data Source window as seen in Figure 4.

Figure 4

Choose the Microsoft ODBC for Oracle driver (version 2.575.1117.0), MSORCL32.DLL.  There are two Oracle drivers, one provided by Microsoft and the other by Oracle (Oracle in XE driver version 10.02.00.01, SQORA32.DLL).  The Oracle driver gets registered when the Oracle 10G Express Edition is installed.  In this tutorial the Microsoft ODBC for Oracle was used.  Begin by clicking on the Button marked Next> in the above window.  In the window that follows, Create New Data Source (shown in Figure 5), type in a name for the data source.  Here it is called Orc10g.

Figure 5

Click on the button marked Next> and you will see the Create New Data Source window which summarizes the action you have taken thus far.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 6

 

Click on the Finish button and this opens up the Microsoft ODBC for Oracle Connect window as shown in Figure 7.  This is where you need to put in the User Name, Password and Server name.  It is assumed that the Oracle database is running.  On the machine used in this tutorial the Oracle Server is set to start when the window starts.  For more information on the Oracle 10g XE server please follow the link shown in the summary section.

Figure 7

 

After typing in the required fields (User Name: hr, Password: hr, Server: xe), click on the button OK.  This will get you back to the Select Data Source window (shown in Figure 8) where you can see the dsn file you created, Orc10g.dsn.

Figure 8

When you click on the button OK, the Oracle login screen comes up as shown in Figure 9.  You type in the Service name [xe], the User Name [hr] and the Password [hr].  It is assumed that you have permissions for this database, if not please review my tutorial.

Figure 9

The authentication may not be immediate and it may take some time.  After your credentials are verified you will see the window "Link Tables" shown in Figure 10.  These are the Tables, Views and a few other objects that are present in the HR database in Oracle xe.

Figure 10

From here you can choose the various tables you need to be linked.  Figure 10 shows the various tables highlighted.  This is done by holding down the control key while the choice is being made. Click on the button OK on the screen in Figure 10 after choosing the tables.  This brings up the Select Unique Record Identifier screen as shown Figure 11.  This is because the object, HR_EM_DE_L, in the Oracle 10G XE is not a table, but a view.

Figure 11

At this point the Employer_ID was chosen and the OK button was clicked.  The implication of this is that the view in Oracle is treated as a MS Access table as seen by the design view in Figure 12.

Figure 12

Clicking on the button OK in Figure 11 brings up another similar dialog (shown in Figure 13), since HR_EMP_DETAILS_View is also a view.  Views were not used in the use interface design, but I showed the process of bringing in views so as to acquaint the reader should he want to use a view to generate his front end application.

Figure 13

Again, the Employer_ID was chosen and the OK button was clicked.  This brings us to the main view of the MDB project as shown in Figure 14.

Figure 14

Figure 14 shows all the Oracle XE tables and views that were linked.  All the linked table names are appended to HR.  If needed, the tables may be renamed.  Of the various tables, for developing the front end, only a couple of tables were chosen, as shown in the Relationships window in Figure 15.

Figure 15