Oracle Forms Builder And Microsoft Access Computer Science Essay

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Oracle Forms Builder is part of an Oracle Developer Suite which also includes Oracle Reports Developer, Oracle JDeveloper, SQL*Plus, Oracle Designer, Oracle Discoverer as well as additional configuration tools. All these components allow building portable applications for various operating system, applications for multilingual support as well as applications that can be monitored remotely.

In contrast, Microsoft Access is intended for developing smaller applications as it is limited in the size and number of users it can support, does not provide the same multi-user transactional support or scalability. All the components of developing an application including data, user interfaces and reports are included in a single development environment.

In order to compare two different development environments, two applications have been created using Microsoft Access 2003 and Oracle Forms Builder 10g. This report aims to compare only those components that relate to developing user interfaces. The application components are described first, followed by the tolls used to work with those components.

Both environments contain a variety of tools necessary for application development including editing layout, object properties and the code behind the application objects. In this section, the individual components of an application built using Oracle Forms Builder as well as the tools used for working with those components are compared to Microsoft Access.

Oracle Forms application is made up of four types of modules:

This type of module contains definitions for form objects such as windows, text items, check boxes, buttons and triggers as well as their code routines. Similarly, each Microsoft Access application form also has an associated module. This is the only module type I have used to program both applications.

Menu module

This module is used to define menu and submenu objects together with the menu item commands. In Microsoft Access, this functionality is achieved through the customisation of toolbars using both built-in and user-defined command actions.

PL/SQL Library module

This module contains the definitions of user procedures, functions and packages, which can be called from other modules in the application. Microsoft Access also contains this functionality and allows to store such code in separate application objects called Module (as opposed to form associated module).

Object Library module

This module stores objects that can be re-used when developing the application, which is very useful in providing a consistent user interface as well as reduction in development time. Such functionality is not available in Microsoft Access.

In addition to the four modules, Forms Builder also provides a number of Built-in Packages, a collection of Oracle supplied procedures, which simplify the development process. Microsoft Access emulates some of this functionality with the use of DoCmd objects, a limited facility in comparison with Oracle Forms, used for example for opening and closing application objects.

Oracle Forms Builder displays the individual application components in Object Navigator and groups them into nodes based on type as follows:

Object Navigator allows viewing items in the following three convenient ways:

Ownership View: Objects are grouped by type

Visual View: Objects are shown organized by containers so that canvases are contained in windows, and items are shown in canvases, etc.

PL/SQL Only: Only objects with associated PL/SQL code are displayed in either view.

In addition to the application components, when Forms Builder connects to an Oracle database, Object Navigator displays various database objects including tables, views and database packages.

Microsoft Access displays its application components using Database Window:

Microsoft Access stores both the database objects as well as application components, which are grouped according to object type. In addition shortcuts to these objects can be to the Favourites or user-defined Groups.

Both tools provide easy to use facilities to manage the application components. Additionally, Object Navigator in Forms Builder lists interface items, which provides additional convenience when developing applications.

Form Components

Every form built with Forms Builder contains at least one window, one canvas, one data block and one item:


Both Microsoft Access and Forms applications are displayed within a master window called Multiple Document Interface (MDI). All other windows are displayed inside this container.


The canvas is used to display items on the screen. Forms Builder uses the term "canvas", whilst Microsoft Access calls it simply a "form", which consists of form header, form detail and form footer. Forms Builder provides four types of canvas:

Content Canvas: This is a default background, which completely fill the window it's in.

Stacked Canvas: This type of canvas can be layered on top of the content canvas as it is used to hide part of the content canvas or display alternate data.

Tabbed Canvas: This type of canvas that has multiple pages identified by tabs. The data is grouped logically into different pages.

Toolbar Canvas: This type of canvas contains custom button bars used as a replacement for the default "smartbar" toolbar.

In Microsoft Access, the default form background is the equivalent of a content canvas, the tabbed canvas functionality is achieved via the use of a tabbed control which is placed on a form and the customisation of toolbars mirrors the usage of a toolbar canvas in Oracle Forms. There is no equivalent of a stacked canvas, although this functionality can be achieved with the use of a frame.

Data Block

A data block connects the form items to the database. A form can have unlimited number of blocks, logically however, it should be split up into smaller forms it contains more than 10 blocks. Items in a block can be displayed on the screen either in form layout (as a single-record) or in tabular layout (multiple records per screen).

The data source for the block can vary from single database table, multiple tables as well as values returned from a PL/SQL Package. A type of block not connected to a database called Control Block can also be created, which can be used to hold reference and working variable values. When a block is associated with a table in the database, Oracle Forms handles the standard DML functions (select, insert, update and delete) automatically without having to write additional code. The standard DML can be overridden when a block is based on PL/SQL code.

The Object Navigator can be used to arrange the data blocks and items for run-time navigation. By default the cursor goes to the first item in the first data block on the form and then moves through each item and block on that form. The navigation can also be coded programmatically.

In addition, Oracle Forms also manages the concept of parent-child relationship. A master block can be created using a wizard that will allow to set up a relationship with the detail block automatically. This can be achieved without the foreign-key relationship between tables present in the database.

Microsoft Access does not have the concept of a data block. A form can be bound to either a table or a query stored in the database. In addition, the access to the data can be accomplished programmatically through the Recordset object in VBA using DAO and ADO libraries. Similarly to Forms Builder, Microsoft Access supports the concept of a master-detail relationship.

Interface Items

Both Forms Builder and Microsoft Access have a variety of items that can be placed on an interface for users to interact with, such as text, check and list boxes, radio groups and buttons. Although they are called by a different name, they provide very similar functionality.

In addition, Forms Builder allows creating pop-up listings of available values for a given field called List of Value (LOV) that can be used to populate other items on the interface based on the selected value. They are based on internal data structures called Record Groups that behave like tables in memory and can be based on a list of static values or on queries.

In Forms Builder, data items can only be created within a data block. They represent a column in a table or a view, display a calculated value, reference value from a Control Block or a value returned by a PL/SQL package. Because Microsoft does not have the concept of a data block, it allows to create items anywhere on the form.

All items have a number of properties including information about their location on the forms, size, colour and font. Both Microsoft Access and Forms Builder have a graphical tool for working with the visual elements of a form.

In Forms Builder, the Layout Editor has a Tool Palette on the left for creating new interface items as well as adjusting the background, fill, text and line colours. The toolbar across the top of the Layout Editor contains buttons for setting font attributes, object alignment, zooming in and out and to changing the layout order as illustrated below:

In Microsoft Access, a form and its elements are edited in Design View. The layout of the editor is very similar to Forms Builder and contains a Toolbox on the left and a properties toolbar on the top of the screen:

The organisation of the tools in these editors seems more intuitive in Microsoft Access. However Forms Builder provides object alignment buttons, which speed up the work when working on the layout of the form.

In addition object properties can be changed programmatically or using properties collection editors. The Property Palette in Forms Builder groups the properties in several separate categories as follows:

Microsoft Access Properties editor is organised into separate tabs according to type of edition that needs to be carried out, such as formatting, data access, events or other. In addition it also has a tab that lists all the properties on one screen:

One of the advantages of Microsoft Access over Forms Builder is that it is also linked to the facilities to edit the functionality behind the controls, allowing to view at a glance the different events the control responds to.


When creating a form, wizards can speed up the development by setting default behaviour and layouts.

Forms Builder provides a wizard for the creation of data blocks, a layout wizard as well as a LOV wizard. Although all wizards are easy to use, they are not intuitive as they require a knowledge of the order in which they can be used, e.g. to launch a layout wizard a data block must be selected or created first. The LOV wizard allows to select and modify an existing Record Group or create a new one, but the same Record Group could be used somewhere else on the form as a record source for a list box. Additionally, if the Layout Wizard was used to build a form, the Update Layout Property of a frame is also set to automatic. This must be changed to manual if any changes to the layout need to be saved.

Microsoft Access also provides a wizard for creating a new form. All the steps that are necessary for selecting the data as well as setting the layout and style are incorporated into one single wizard making it a lot more intuitive to use. In addition, Microsoft Access provides wizards for the creation of new interface items such as list boxes and command buttons and can be turned off if desired.

One very useful feature of the above tools is that they are all linked together and changes made in one of them are automatically displayed in the other tools.

Adding Functionality

Applications built in Forms Builder and Microsoft Access can be customised to respond to user's interaction with a form, keyboard events generated by the user pressing a function key, internal processing events and user-defined events.


Triggers are executed in response to such events. They can replace the default behaviour or add custom logic to the form. The level at which a trigger is defined determines its scope e.g. item level or block level. Some triggers can only be defined at one level, some at multiple levels. In Forms Builder, triggers are written using standard PL/SQL syntax. They can be divided into several categories such as block processing triggers, interface event triggers, navigational triggers, transactional triggers, validation triggers, master-detail triggers and key triggers.

Microsoft Access emulates this functionality by providing event procedures that run in response to an event initiated by the user, program code or triggered by the system, e.g. OnClick, AfterInsert, BeforeUpdate, OnClose etc. This functionality can be programmed using expressions, macros or VBA code.


Built-ins are functions or procedures that allow PL/SQL code to interact with Oracle Forms objects in PL/SQL. They extend PL/SQL with form-specific functionality, for example CLEAR_BLOCK for clearing the data block, NEXT_ITEM for navigation and EXECUTE_QUERY for query execution.

Such functionality is provided in Microsoft Access by the DoCmd object, which has a number of methods including GoToRecord, Minimise, OpenForm, Close and many more. This can be coded with macros or VBA.

Forms Builder provides a PL/SQL Editor, which allows to enter code for adding above functionality as well as to compile code objects such as event triggers, subprograms (functions and procedures), menu item commands, menu startup code, and packages:

Microsoft Access has 3 different tools for coding additional functionality. The Code Builder, which is a Visual Basic Editor:

A Macro Builder allows entering the required Action and Action Arguments:

And finally, the Expression Builder for entering common expressions that reference the names of fields and controls in the database, as well as many available built-in functions:

Overall, Microsoft Access provides a set of flexible tools that can be used by a wide range of users. The Forms Builder is a more complex tool that relies on the specialist knowledge of PL/SQL. It does however aid the programmer with the Syntax Palette that displays the constructs of PL/SQL language elements and built-in packages allowing the user to copy the selected syntax into a program unit.

System Variables

In addition, Oracle Forms provides set of system variables that allow access to run-time information about the application, the status of the form, date and time, block and record status, mouse position and many more. Microsoft Access has two types of variables: local variables that persist within a form and instance variables, which persist for the duration of the Microsoft Access database session. The biggest disadvantage is that both types of variables must be defined by the user first.

Reusable Components

In addition to the above functionality, unlike Microsoft Access, Oracle Forms Builder supports the use of reusable components that allow the development of standard layouts and the use and behaviour of GUI elements for common look and feel. This avoids duplication of work and aids easy maintenance especially to implement design changes.

Visual Attribute Groups (VAG)

VAGs are user-defined sets of properties that control the appearance of an object by defining properties for fonts, colours and patters. They are a "shortcut" for applying properties to an item without updating each property individually. Changing a property in the VAG automatically propagates the change to each item that references that VAG. VAGs are created in the Object Navigator under Visual Attributes node. They do not have any physical representation.

Property Classes

VAGs are used for setting visual properties for colours, fonts and patterns. In addition, to define templates for other attributes such as size, location and behaviour of objects, Property Classes are used. These multiple properties and their values are assembled into groups and groups assigned to various form objects. Objects that have properties assigned to them in this way are called subclassed items.

Properties can be added and removed from the Property Class property sheet. As with VAGs, if the properties of the Property Class are changed, the new values are propagated to all the subclassed items.

Object Groups

Because VAGs and Property Classes are set up in individual forms, in order to implement a system-wide change, each form would have to be updated. To avoid this, any related objects are bundled together in an Object Group, which allows them to be used by another module. Adding or removing any object from the Object Group automatically updates any form that includes that group.

For example, window, canvas, blocks, items and triggers containing the required logic for scheduling appointments in a calendar can be packaged in an object group and then easily copied to other forms in one simple operation.

Object Libraries

Object Libraries are not a part of the application itself. They are used to create, maintain and distribute predefined reusable objects that can be dropped into a form and enforce standards for the look and behaviour in an application. Any Forms object can be stored in an Object Library, except for a form itself. When a library is created in the Object Navigator and items are dragged and dropped into it, they become available to Forms Builder. The objects can be then dragged from the Library onto the destination form. This object can then be independent of the library or linked to the master in the Object Group.

Object Libraries can be updated and the changes immediately are available to the development team. You can associate multiple Object Libraries with one application. For example, you may have a generic library that contains corporate-wide standards, and a smaller library for project-specific items.

Smart Classes

A Smart Class is a type of object, which acts as a template to create objects with the same properties, or to apply the properties to an existing object. An object based on a Smart Class inherits its functionality and appearance. Smart Classes are used to distribute design standards to an entire development team.

Shared PL/SQL Code

One of the most important ways to speed up development is to share the code that is written in triggers. This is achieved by using Program Units, PL/SQL Libraries, and by referencing PL/SQL code in the database.

PL/SQL Libraries are collections of packages, procedures, and functions that are deployed as separate modules in the application. Because they are outside of the form scope, they use special built-ins to reference the objects within a form indirectly. A PL/SQL Library is deployed as an executable file. Program Units provide means of writing PL/SQL code that can be called from multiple triggers. By putting Program Units into PL/SQL Libraries they can be shared across multiple forms.

Finally, Forms can reference any code in the database. However, PL/SQL code in the database cannot reference forms items because the PL/SQL engine in the database does not understand the Forms built-ins.


Both Oracle Forms Builder and Microsoft Access provide flexible development environments. Both can be used to develop simple applications, however because the Forms Builder is part of a collaborative environment it also provides high level support for such development tasks. In addition, the applications build using Forms Developer integrate with Oracle database, which allows to create very complex and robust solutions.