Creating an MS Access 2003 Front End for an Oracle 10g Express Edition Database
 
Published: 28 Jun 2006
Unedited - Community Contributed
Abstract
In this tutorial Jayaram demonstrates the steps involved in creating a MS Access front end for an Oracle 10G XE back end. The user interface on MS Access takes the form of a Data Access Page hosted on the Intranet.
by Jayaram Krishnaswamy
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 50220/ 94

Introduction

Enterprises have data stored in different kinds of databases from flat-files to relational databases. It is often necessary to generate reports from disparate databases.  The rapid application development that is possible with MS Access allows one to rapidly deploy the report created to the intranet web site without writing so much as a line of code.   This tutorial shows you the steps involved in creating a MS Access front end in the form of a Data Access Page for an Oracle 10g Express Edition database.  Linked tables are used which are at the heart of distributed queries.  The MS Access project links to the Oracle XE tables using an ODBC connection.  The tutorial also shows how to create a data access page using data from the Oracle XE and host it on the intranet server.

Oracle 10g Express Edition [xe] is Oracle's latest software, downloadable from this link.  It is an easy to administer, free to develop, deploy and distribute product ideal for those who are interested in working with a cutting edge RDBMS.  It has a sample database with which the front end on MS Access has been developed.  After downloading and installing the Oracle 10G XE software, one has to use the Admin credentials to login and give the login permissions to the front end developer.  This is described in considerable detail in the provided link.  Figure 1 shows the object browser in Oracle 10G Express Edition listing all the tables in the sample database, HR.

Figure 1

Creating an ODBC DSN to Oracle XE

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

Creating User Interface Objects for the Front End

From here on it is just vanilla MS Access front end development.  We first create a query using the query designer as shown in Figure 16.  This section is shortened as the process is straight forward.

Figure 16

Figure 17 shows the selected columns in the above query arranged in a tabular form when the query is executed.

Figure 17

Creating a Data Access Page

Next, we create a data access page using the Page wizard which brings the screen shown in Figure 18.  You can choose to display the fields (columns) you want by clicking on the single arrow [>] to transfer the columns you desire one-by-one, or use double arrows [>>] to transfer all available fields to the right pane.  This is a standard selection UI in Microsoft Products.

Figure 18

When you click on the Next> button in Figure 18 you will see the next window of Page Wizard shown in Figure 19 where you add the grouping levels on the fields.  As you can see, the Country_Name is the first grouping level followed by the sub-level City under it.

Figure 19

Clicking on the button Next> takes you to the window Grouping Intervals in Figure 20 where you may want to choose some grouping interval for the group-level fields.  For example, if you were to choose 1st Letter for the Country_Name field, your display will show only the first letter for the Country Name.  For this choice, both Canada and China will show up under the letter "C."

Figure 20

When you click on the button OK you will be taken to the next window of Page Wizard shown in Figure 21.  You will need to choose the sorting of the records.  Again, this is another standard UI for sorting.  In the screen shot shown in Figure 21, the Last_Name is chosen to be sorted in the ascending order.

Figure 21

Clicking on the button Next> takes you to the Page Wizard window shown in Figure 22 where you provide a name for the data access page's title.  Here you have the option of either opening the page as is or modifying the page's design with or without applying a theme.  If needed, you display Help on working with this page.  Here the option shown in Figure 21 is chosen.

Figure 22

When you click on the Finish button in the window shown in Figure 22, the Data Access Page will open in design view as shown in Figure 23 with default, Page 1: Data Access Page.  The grouping you chose has been faithfully represented in the design showing that the wizard did a good job of implementing the information you provided to the wizard.

Figure 23

For previewing the Data Access Page, it needs to be saved.  You may save from the menu item File by choosing the Save As drop-down menu.  This brings up the window Save As shown in Figure 24.  By default the page gets saved to the My Documents folder in Windows XP Professional.  However, you need to provide a name.  Here it is named OrcEmp.

Figure 24

When you click on the button OK in the screen shown in Figure 24, the Save As Data Access Page window will show up with My Documents in default for the Save in: field.

Figure 25

Since this page is going to be hosted on the intranet site, the C:\Inepub\wwwroot folder was chosen as the directory for the saved data access page, OrcEmp.htm.  This brings up the MS Access message shown in Figure 26.  If the button Yes is clicked, the root directory will become the default for all Data Access Pages.  In this tutorial the option No was chosen.

Figure 26

This saves the file to the root directory.  The orcEmp.htm finds itself in the default website as shown in Figure 27.  This file may now be browsed from the intranet website, which in the present case is the localhost.

Figure 27

However, when you try to browse the data access page you may get the following two warnings. Click on the button OK on both the windows shown in Figure 28 and Figure 29.  The first one deals with the security regarding the data source and the second deals with access permissions.

Figure 28

Figure 29

When you click on the button OK in Figure 29, you will meet with the Oracle login page once again.  You use the same credentials used earlier.

Figure 30

Since you are authenticated, you will see the Data Access Page OrcEmp.htm displayed on your browser at the address http://localhost/OrcEmp.htm.  The grouping levels have been fully expanded.  Using the [-] sign they can be collapsed.

Figure 31

 

The data access page is connected to the data through the field list associated with it, shown in Figure 32.  By highlighting and right-clicking each of the fields (columns), their properties can be accessed.  Changes to the display format of the data access page can be accessed by clicking on the controls in the design view of the page, shown in Figure 33.

Figure 32

In Figure 33, Country_Name was highlighted and the various properties one could access are shown in the drop-down list.  Page, Group_level, section and element properties can be managed by the dialogs that ensue when any of these options are chosen.  It is also possible to call up the Microsoft Script Editor, which can be used for dynamic interaction with the page through the DHTML script support using the client objects and scripts.

Figure 33

Figure 34 shows some cosmetic changes applied to the screen (shown in Figure 31) by accessing the Data Access Page's properties.  The page's heading has been modified, as well as some of the properties of the other fields.  While Data Access Pages can be modified at design time, as described above, properties at run time can be modified using the scripting support provided by the Microsoft Script Editor and the Visual Basic Editor built into the data access page (see Reference link 2 in summary).

Figure 34

Summary

Creating an ODBC dsn is central to linking the Oracle tables in Microsoft Access.  Once the tables are linked, it is entirely up to MS Access as to how it is displayed either in a report, a data access page or both.  Microsoft Access 2003 and Oracle 10g Express Edition were used in this tutorial.  For duplicating the result of this tutorial or generating a customized user interface, reviewing the following links will be very helpful.

1. Oracle 10G Express Edition

2. Data Access Pages

3. ODBC Linking



User Comments

Title: help   
Name: shirish
Date: 2011-10-30 2:57:18 AM
Comment:
figure in the above document is not visible??
so please upload new web page here
Title: Shaik   
Name: Sabir Hussain
Date: 2011-02-12 11:36:17 PM
Comment:
This is really great article for those who willing to know like these things in ever their life..

Really great stuff......
Title: shri   
Name: sreedhar reddy
Date: 2010-06-01 2:48:46 PM
Comment:
great article for those to connect to oracle using access.thanks a lot






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-20 7:17:25 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search