As Published In
Oracle Magazine
May/June 2007

DEVELOPER: Browser-Based


Application Migration Workshop

By David Peake

Migrate from Microsoft Access to Oracle Application Express.

Many small and medium businesses use Microsoft Access to manage mission-critical business functions. As businesses grow or their requirements expand, however, they may need to migrate their existing Access applications to a more-robust architecture and a function-rich tool. This column describes a migration path from Access to Oracle Application Express running on an Oracle database. This Oracle solution migrates the Access data, forms, and reports to a pure browser-based application.

Overview

Application Migration Workshop is a standard feature of Oracle Application Express 3.0. It focuses on recovering the design of existing Access applications and generates Oracle Application Express applications based on the recovery.

Application Migration Workshop provides the following capabilities: 

  • Creates a view into all application metadata captured from an Access database

  • Identifies tables without primary keys or user interface defaults

  • Identifies invalid Oracle views (originally Access queries)

  • Includes only those Access objects you are interested in migrating

  • Verifies that the SQL code is valid Oracle syntax for Access forms and reports that are based on a SQL query

  • Includes the options of generating an Oracle Application Express application based on your selected Access forms and reports or a maintenance application based on every table and view selected

  • Enables you to create multiple migration projects for a given Oracle Application Express workspace

  • Enables you to migrate applications from Access 97, 2000, 2002, and 2003


Prerequisites

The entire migration process requires the following tools:

Oracle Application Express 3.0. The Application Migration Workshop feature in Oracle Application Express migrates Access forms and reports to Oracle Application Express. You can download Oracle Application Express and a comprehensive installation guide from oracle.com/technetwork/apex.

Oracle SQL Developer Migration Workbench Release 1.2 or later. The workbench migrates the Access schema and data to Oracle Database. You can download the migration workbench and the Access Plug-In from oracle.com/technetwork/products/migration.

Exporter Tool. The exporter tool is written in Access and generates two files that can be interpreted by Oracle Application Express and Oracle Migration Workbench. In Oracle Application Express, select Application Migrations to download the appropriate version of the exporter tool for the version of the Access database being migrated. Install the exporter tool on a computer that can access the Access database, following the help file instructions included with the download.

How to Migrate Your Applications

To migrate applications from Access to Oracle Application Express, perform the steps described below, in the order given. Figure 1 shows the flow of the process.

 

figure 1
Figure 1: Microsoft Access to Oracle Application Express migration workflow


The migration process consists of the following steps: 

  1. Export Access metadata

  2. Migrate the Access database to Oracle Database

  3. Create an Oracle Application Express workspace

  4. Create a migration project

  5. Review your recovered objects

  6. Generate the Oracle Application Express application

  7. Customize your Oracle Application Express application (optional)


Step 1: Export Access metadata. Extract the data and application definition from your existing Access application. Run the exporter tool (.mde file) to start the Access application with options for which files to produce. Select Both Files , and enter the filename of the Access application being migrated and the output directory.

The exporter tool generates the following files: 

  • An XML (.xml) file that describes the database schema

  • A SQL (.sql) file that contains all metadata for the application

Step 2: Migrate the Access database to Oracle. Now start Oracle SQL Developer, and select Migration -> Capture Exporter XML to start the migration workbench. Enter the XML file generated in step 1. The migration workbench will create the physical database instance based on the Microsoft Access table definitions.

Step 3: Create an Oracle Application Express workspace. Configure a new workspace in Oracle Application Express. Using the administration interface, create a workspace ( Manage Workspaces -> Create Workspace ), name the new workspace, and associate your newly migrated database schema (from step 2) with it.

Step 4: Create a migration project. In step 1, you created a second file, a SQL file, that contains all the metadata of the application. Using this file, create a migration project for your full-featured Oracle Application Express application.

To create the application, first log in to the Oracle Application Express workspace created in step 3 and click Application Migrations on the home page. Click Create Project , to initiate the Create Migration Project wizard; enter the Project Name; and associate the SQL file. Once the wizard has finished, the Migration Project page (shown in Figure 2) appears, providing a high-level overview of the Access objects recovered from your Access database.

 

figure 2
Figure 2: Migration Project page


Step 5: Review your recovered objects. The Migration Project page initial list consists of the Access application metadata that is recovered, both valid and invalid. For an object to be included in the migration, it must have a status of Valid. By default, all objects with Valid status are selected for migration. You can also fix any objects identified as invalid, so that they can be included in the migration, and you can correct tables without primary keys and objects without user interface defaults, to maximize application design recovery. Note that if you choose not to include a table or a query, all forms and reports based on that object are automatically excluded from the migration.

Begin reviewing the recovered application objects by clicking Tables to display a list of the tables recovered. Clicking any of the Oracle tables listed brings up a detail screen for that table, showing the table details, primary key information, and column details. If the primary key for a table is missing, click the Create Primary Key task and complete the wizard to generate the primary key. Similar tasks allow you to add indexes and set UI defaults.

Next, review the queries (Oracle views) recovered, correct invalid queries, and select the ones you want to include in the migration. From the Migration Project page, click Queries to display a list of the queries recovered. To attempt to compile invalid queries, select Attempt to compile invalid queries (below the list). You can manually correct any queries that are still invalid, by clicking on that Oracle view, editing the SQL, and then compiling. All valid queries you want included in your migrated application should be checked in the list.

Similarly, you can identify invalid forms and reports and list additional information, such as their source type and source name. On the Migration Project page, click either Forms or Reports , to display the corresponding list. Both include a link below their lists, Attempt to compile invalid SQL queries , which you can click to attempt to compile invalid objects. When you select the Source Type corresponding to an invalid form or report, you can edit the source code and then validate it. For forms based on tables, there are additional Migrate To options ( Form , Tabular Form , or Report and Form ), one of which you should select to specify the generated layout. After you update the forms and reports, select the ones you want to include in the migration.

Step 6: Generate the Oracle Application Express application. After validating and updating objects, generate the application in Oracle Application Express. You can create an application based on valid forms and reports or create a maintenance application based on valid tables and views.

There are three tasks for generating the application on the Migration Project page:

Generate application defaults. Specify the defaults that will be used to generate Oracle Application Express applications. These include Tabs, Authentication, Theme, and Globalization.

Generate application. Create Oracle Application Express pages based on the forms and reports you have included for the project.

Generate maintenance application. Create Oracle Application Express pages specifically for maintaining data in your base tables.

Use the Generate Application task to produce an application that replaces your Access application. Click Generate Application , and the Generate Application wizard will display a list of the pages that will be created. Click a Page Name to update the page definition and page items for the selected page. Click Delete Page to delete any unwanted pages, and click Create .

If you are interested in creating an application for maintaining the data in your migrated database, use the Generate Maintenance Application task. Click Generate Maintenance Application, and the Generate Maintenance Application wizard will display a list of the pages that will be created. Click a Page Name to update the page definition and report columns/page items for the selected page. Click Delete Page to delete any unwanted pages, and click Create .

After you run the Generate Application or Generate Maintenance Application wizards and have created the application, the last page of the wizard provides the Run Application or Edit Application options. The migrated Access data is now accessible from the Oracle Application Express application and will be maintained in an Oracle database.

Next Steps



READ more about
Oracle Application Express
migration

 DOWNLOAD Oracle Application Express

 VISIT the Oracle Application Express forum on Oracle Technology Network

Step 7: Customize your Oracle Application Express application (optional). Once you have created your initial application, you can take full advantage of Oracle Application Express Application Builder to further enhance and improve the application. Once you have all your previously disparate datasources available in one Oracle database, you may want to build new functionality that integrates and streamlines business functions or perform cross-checks. You may also want to add more regions or pages, review authorization methods, add new functionality, or build better reports on existing information.

Conclusion

The easy-to-use application building tools of Oracle Application Express significantly improve productivity and allow frequent changes as business requirements evolve. It is the perfect tool for consolidating disparate department databases into a centralized database.

Moving data from desktop databases and spreadsheets into an Oracle database makes it possible to manage the security of the data centrally. Most important, by providing Web-based user interfaces for these database-driven applications, you are empowering your users to make the changes they need to make and giving them access to the latest data, so they can make informed decisions.

 


David Peake (david.peake@oracle.com) is a principal product manager in Oracle's Server Technologies division. He has been with Oracle since 1993.


Send us your comments