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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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 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.