Oracle Application Express Application Migration Workshop

Oracle Application Express

Return to Oracle APEX Home

The Oracle Application Express Application Migration Workshop is a feature of Oracle Application Express (Oracle APEX) that assists customers in migrating Microsoft Access applications to Oracle APEX. It works in harmony with the Oracle SQL Developer Migration Workbench to provide a total solution for your Access Migration requirements. You first use the Migration Workbench to migrate your schema definition and data and then use the new Migration Workshop to migrate your forms and reports. The last step is refining your new application taking advantage of all the power and capabilities of Oracle APEX.


MS Access Screen	Application Express Screen

Oracle APEX presents a credible alternative to Access with the added advantage of a completely 100% browser based environment together with the performance, security and scalability inherent in the Oracle database. Our migration solution is no 'magic bullet'. For example, it does not migrate the logic contained within any Visual Basic code you might have in your Access Application. The solution is focused on recovering the design of existing Access applications and generating Oracle APEX applications based on the findings. This sometimes requires manual intervention after the migration tools perform the bulk of the migration work. You can also take advantage of the opportunity to improve the migrated Access database schema, which can yield significant benefits to the resulting Oracle APEX applications.

Features of the Application Migration Workshop include:

A view into all application metadata captured from Access, thus enabling comparison with the results of the schema migration.
Identification of tables without primary keys or Oracle APEX UI Defaults (used to default the new application).
Identification of invalid Oracle views or Access queries that the workbench failed to parse.
Option to include only those Access objects that you are interested in migrating with the additional benefit that the Workshop will understand the dependencies between those objects. For example, if you exclude a view (Access Query) then any Access reports based on that view are also excluded.
Syntax check of SQL query-based Access reports.
Optional generation of a "maintenance application" consisting of a report and form for every table and view selected.
Generation of an application based on your selected Access forms and reports.
Supports migration from Microsoft Access 97, 2000, 2002, and 2003.

To see the details of a migration, access our Migration Tutorial.

Migration Process Overview

The diagram below outlines the migration process. To extract your Access data and application definition, you use Oracle's Exporter. One of the resulting files, MyAccess.xml, is turned into Oracle database objects and data using the Oracle Migration Workbench. Another file, MyAccess.sql, contains the application definition and is loaded into Application Express. At this point, you can use the Migration Workshop to refine the application definition and generate an Application Express application.

Typically, both the data model and application definition are modified to improve deficiencies and to take advantage of all the features of Application Express (PDF Printing, Flash Charts, etc). Once you are happy with your application, you run your new application using the same web browser you used to develop it.

Why Migrate to Oracle Application Express?

Organizations use Access for many purposes. Access may be appropriate for desktop database applications with small numbers of users. For applications that contain sensitive data, have larger user communities, or could benefit from a Web interface, Access may no longer be appropriate. These are the types of applications that are candidates to migrate to Oracle and Application Express. Below is a matrix of features highlighting architectural differences. It also includes many of the pain-points you will experience when you outgrow your Access database.

Feature	Application Express	Microsoft Access	Description
4GL Languages	SQL and PL/SQL	SQL, Macros and VBA	Application Express uses server-side PL/SQL. Access uses Macros or Visual Basic for Applications (VBA).
Development Interface	Web Browser	Windows Client Tool	Application Express only requires a web browser to develop applications. Access requires Access to be installed locally on a developer's Windows desktop.
Runtime Interface	Web Browser	Windows Client Tool	Application Express is invoked from a Web browser with its user interface being HTML and JavaScript. The default user interface for Access is Windows with some limited HTML capability for Forms (Access Pages).
Page Layout	Relative	Exact	Application Express uses HTML-relative positioning. Access uses exact positioning.
Client-side Field Control	JavaScript and AJAX	Macros / VBA	Application Express supports declarative page-level validation and event processing. Programmatic field-level validation and event processing requires JavaScript and AJAX. Access supports fine grained validation and event processing through the use of Macros and VBA.
Web Service Support	Yes	No	Application Express supports the calling of Web Services, for example BPEL. Web Services facilitate interoperability with Service Oriented Architecture (SOA) enabled components.
Locking	Optimistic	Pessimistic	Due to its asynchronous architecture, Application Express uses an optimistic locking model. Access, via the Jet engine, applies a pessimistic locking approach.
Database Connections	Asynchronous	Synchronous	Application Express does not transparently allow transactions to span page views. Application Express programmatically supports transactions spanning page views using collections. Access uses a synchronous interface to the Jet engine to allow transactions to span multiple windows.
Max Database Size	Unlimited	2 GB	Application Express, being part of the Oracle database, has no limitations on database size. Access database size is limited to 2 GB.
Concurrent Users	1,000's	255 (Jet DB limit)	Application Express, due to it architecture, can support 1000's of concurrent users on a small 2 CPU server. Access has a maximum of 255 concurrent users, but recommends no more than 20 concurrent users
Backup & Recovery	Centralized and managed by Oracle DBAs	Distributed, typically with non-professional management	The Oracle Database has a host of backup and recovery options that can be employed. Application Express applications are stored as meta-data within the Oracle database. Therefore, the application definition is protected along with the data. Access applications are often scattered across the enterprise with varying backup and recovery capabilities. The management of these processes is typically left to the developers or LAN Administrators in each department.
Authentication	Secure	Vulnerable	Application Express supports whatever single sign-on authentication schema your company implements. It also provides multiple authorization schemes for authenticated users. Access security is primarily file system based security. Access passwords are stored within the access mdb file and are vulnerable to Access password recovery utilities.
Client Platform	Windows, MAC, Linux	Windows	Application Express will run on a variety of different platforms facilitated by a number of different browsers. Access runs on Windows only.
Client Software	None	Access	Application Express requires no special client software to run. Access requires an Access or Office installation.
Data Management	Consolidated in an Oracle database	Distributed across multiple files	Application Express encourages the consolidation of data and applications into a single hosted server. Access leads to information fragmentation, as each application is contained in one or more files.
Concurrent Development	Yes	No	Application Express supports multiple developers within a workspace where these developers can be working on the same, or different, applications. Access normally has a single developer working on a single mdb file at a given time.