3 Migrating a Microsoft Access Application

3.1.1 About the Process of Migrating a Microsoft Access Application

The following illustration outlines the entire process of migrating Microsoft Access application to Oracle Application Express.

The migration process consists of the following steps:

• Step 1: Exporting Microsoft Access Metadata

• Step 2: Migrating the Microsoft Access Database to Oracle

• Step 3: About Creating a Workspace and Adding Oracle Application Express Users

• Step 4: Creating a Migration Project

• Step 5: Reviewing Retrieved Objects

• Step 6: Generating an Oracle Application Express Application

3.2.1 About Downloading the Exporter Tool

To export your metadata from Microsoft Access, download the correct version of the Exporter tool, as explained in this section. Then, run the Exporter and extract the metadata from the Microsoft Access .mdb file. The metadata contains the necessary database and application schema information.

The export process creates two output files:

• database (.xml file)

• application (.sql file)

3.2.2 Exporter Tool System Requirements

To use the Exporter tool, the Microsoft Windows operating system where the Exporter tool runs must include the following libraries:

• Microsoft DAO 3.6 Object Library

• Microsoft ActiveX Data Objects 2.5 Library

3.2.3 Downloading the Exporter Tool

To download the Exporter tool for Microsoft Access:

1. Access the workspace you created for your conversion project.

2. Click the Application Builder icon.

3. Click Migrations on the right side of the page.

4. Under Tasks, click Download Exporter for Microsoft Access.

5. In the Download column, click the zip file that corresponds to your version of Microsoft Access. For example, download the omwb2003.zip file if you are using Microsoft Access 2003.

6. Save the file.

7. Unzip the file. You must replace the following files with the updated versions in the msaccess_exporter directory where you unzipped the Exporter tool:

   • schema.dtd file

   • Exporter tool file: omwb<version>.mde

   • online help file: omwb.chm

   Be sure to invoke the export from this directory.

3.2.4 Exporting Your Metadata

To export your metadata, follow the instructions found in the help file for the Exporter tool. To find the instructions, do one of the following:

• Launch the Exporter tool, and click the Help button.

• Open the help file (omwb.chm) contained in the Exporter zip file.

The instructions appear in the topic called Exporter Overview.

3.3.1 Migrating the Database to Oracle

To migrate the Microsoft Access database to Oracle:

1. Start Oracle SQL Developer. For example:

   1. Create an Oracle user named MIGRATIONS with the default tablespace USER and temporary tablespace TEMP and grant it at least RESOURCE, CREATE SESSION, and CREATE VIEW privileges.

   2. Create a database connection named Migration_Repository that connects to the MIGRATIONS user.

   3. Right-click the connection, select Migration_Repository, and then select Migration Repository and then Associate Migration Repository to create the repository.

2. Load the database metadata (.xml file) created in step 1. On the Oracle SQL Developer toolbar, select Tools, Migration, and then Capture Microsoft Access Exported XML.

   This captures the schema and creates a Captured Model of the Microsoft Access database.

3. Convert the captured database schema to Oracle. Right-click Captured Model and select Convert to Oracle Model.

4. Generate the migrated Oracle database schema. Right-click Converted Model and select Generate.

   The generated DDL statements should execute against your Oracle database instance, to generate the migrated schema objects.

For more information about Oracle SQL Developer migration capabilities, see:

http://www.oracle.com/technetwork/database/migration/index.html

3.3.2 Verifying If Generated DDL Executes Against the Appropriate Instance

When using Oracle SQL Developer migration capabilities, you must verify that the generated DDL statements are executed against the same instance where Oracle Application Express 4.0 or later is installed. If you select a Least Privilege Schema Migration, the migrated objects can be created in an existing schema on your database instance. Otherwise, a schema of the same name as the captured database (for example, Northwind) is created on your database instance.The migrated Oracle database schema objects must be in the same instance where Oracle Application Express is installed. If they are not, you will not be able to complete the next step.

3.6.1 About Reviewing Retrieved Objects

The initial list retrieved objects consists of the Microsoft Access application metadata that is retrieved, both valid and invalid. To include an object, it must have a status of Valid. By default, all objects with a Valid status are selected. From within Application Migration, you can fix objects identified as invalid so that they can be included. Since the Application Migration also identifies tables without primary keys and objects without user interface defaults, you can correct those situations to maximize application design recovery.

3.6.2 Reviewing Retrieved Tables

The Application Migration identifies invalid tables without primary keys and those without user interface defaults, which you can add before migrating.

After you update the tables, select the ones you want to include in the migration. If you do not include a table, all forms and reports based on the table are excluded from the migration.

To review retrieved tables:

1. From the Application Migrations page, click the project name.

2. On the project page, click Tables.

   The Tables page appears, showing the status of the objects ready for migration:

   • Oracle Table - Identifies the corresponding Oracle table, which defaults to the Microsoft Access table name in all capital letters.

     Note that the name may differ from the original one because of the collision management facility in Exporter tool. For information about naming guidelines and restrictions, click Help in Oracle SQL Developer and go to the Frequently Asked Questions section.

     If the Microsoft Access object was not successfully migrated to Oracle, then this field will not have a corresponding Oracle table name. Instead, it will contain a link to a page where you can create a corresponding Oracle table.

   • Primary Key - Indicates if a primary key exists for the table.

     A table without a primary key is considered invalid in Application Migration. You can create a primary key at this point. All tables you want to migrate should have a primary key.

   • Foreign Key - Indicates if a foreign key exists for the table.

     If you know a relationship exists between two tables, you should create a foreign key. You can do this in Object Browser by creating a Foreign Key Constraint. For more information, see "Managing Database Objects with Object Browser" in Oracle Application Express SQL Workshop Guide.

   • UI Defaults - Indicates if user interface defaults are set for the table.

     User interface defaults populate initial values for region and item properties. Using user interface defaults provides consistency across multiple applications or across multiple pages in an application.

   • Status - Table status as either Valid or Invalid.

     Only valid tables can be included in the migration.

3. To create a table:

   1. On the Tables page, click the link in the Oracle Table column for the table you want to create.

      The Oracle Table page appears.

   2. Click the Create Table tasks option.

      The Object Browser appears.

   3. Click the Create button.

   4. From the list of object types, select Table.

   5. Follow the on-screen instructions.

   Tip:

   If the associated Microsoft Access table was not successfully migrated to Oracle, review the Oracle Migration Workbench log file for further information.

4. To add a primary key:

   1. On the Tables page, click the Oracle table name.

   2. From the Tasks list, click Create Primary Key.

   3. For Constraint Details, fill in the information.

      Tip:

      To review the list of existing columns or constraints, expand the Existing Columns or Existing Constraints regions.

   4. Click Next.

   5. Confirm the information and click Create.

5. To add an index:

   1. On the Tables page, click the Oracle table name.

   2. From the Tasks list, click Create Index.

   3. Select the type of index you want to create on this table.

      For indexing NUMBER, VARCHAR, and DATE, select Normal. For indexing CLOB columns, select Text.

   4. Click Next.

   5. For Index Definition, fill in the information.

   6. Click Next.

   7. Confirm the information and click Create.

6. To set user interface defaults:

   1. On the Tables page, click the Oracle table name.

   2. From the Tasks list, click UI Defaults.

   3. On the UI Defaults page, click Create Defaults.

   4. On the Create Table Dictionary Defaults page, click Create Defaults.

      The Table Dictionary page appears. Note that you are now working within SQL Workshop. SQL Workshop is a component of Oracle Application Express with utilities to load and unload data from an Oracle database, generate DDL, view object reports, and restore dropped database objects.

   5. On the Table Dictionary report, click the Oracle table name in the Object Name column.

      The Table and Column Properties page appears, listing column information as it will appear in forms and reports.

   6. To edit the information, click the Column name.

      The Column Defaults page appears. You can update the column label, change the sequence of how the columns appear by default, and so on.

   7. Click Apply Changes to save your updates.

7. To include tables in the migration, select them in the left column.

8. Click Apply Changes to save your selections.

3.6.3 Reviewing Retrieved Queries

The Application Migration identifies invalid queries and those without user interface defaults, which you can set before migration.

After you update the views, select the ones you want to include in the migration. If you do not include a query, any forms or reports based on the query are excluded from the migration.

To review retrieved queries:

1. From the project page, click Queries.

   The Queries page appears, showing the status of the objects ready for migration:

   • Access Query - Indicates the corresponding Oracle view, which defaults to the Microsoft Access query name in all capital letters.

     If the Microsoft Access object was not successfully migrated to Oracle, then this field will not have a corresponding Oracle view name. Instead, it will contain a link to a page where you can create a corresponding Oracle view.

   • Status - Query status is either Valid or Invalid.

     Only valid queries can be included in the migration.

   • UI Defaults - Indicates if user interface defaults are defined for the query.

     User interface defaults populate initial values for region and item properties. Using user interface defaults provides consistency across multiple applications or across multiple pages in an application.

2. To run a bulk process that attempts to compile all invalid queries, click Attempt to compile invalid queries.

   Using this option can validate some queries that show a status of invalid when initially migrated.

3. To create a view:

   1. On the Queries page, click the link in the Oracle View column for the view you want to create.

      The Oracle View appears.

      Tip:

      The original Access query syntax can be copied from the Access Query region to the editor. You may need to edit the syntax to ensure it is valid Oracle syntax before compiling.

   2. Click the Compile button.

   3. From the list of object types, select View.

   4. Follow the on-screen instructions.

4. To edit a query:

   1. On the Queries page, click the Oracle view you want to edit.

   2. Click Compile to find the invalid part of the query.

      The Microsoft Access Query syntax appears in the edit window. It may require some modification to make it valid Oracle syntax.

   3. Click Access Query to review the initial query and compare it to the converted query.

   4. Update the query and recompile it.

   5. When it is validated, click the Queries breadcrumb.

   6. To include this validated query, select it in the left column on the Queries page and click Apply Changes.

5. To set user interface defaults:

   1. On the Queries page, click the Oracle view.

   2. In the Tasks list on the right, click UI Defaults.

   3. On the UI Defaults page, click Create Defaults.

   4. On the Create Defaults page, click Create Defaults.

      The Table Dictionary page appears. The Table Dictionary page appears. Note that you are now working within SQL Workshop. SQL Workshop is a component of Oracle Application Express with utilities to load and unload data from an Oracle database, generate DDL, view object reports, and restore dropped database objects.

   5. To edit the information, click the object name.

      The Table and Column Properties page appears.

   6. To edit the information, click the Column name.

      The Column Defaults page appears. You can update the column label, change the sequence of how the columns appear by default, and so on.

   7. Click Apply Changes to save your updates.

6. To include queries in the migration, select the Include check box in the left column on the Queries page.

7. Click Apply Changes to save your selections.

3.6.4 Reviewing Retrieved Forms

The Application Migration identifies invalid forms and lists additional information, such as the form's source type and source name. For valid forms with a source type of table, you can select the type of object you want the form to become within Oracle Application Express: form (default), report and form, or tabular form.

Microsoft Access forms based on a query are migrated to Oracle Application Express forms. Microsoft Access forms based on a SQL query are migrated to Oracle Application Express reports.

After you update the forms, select the ones you want to include in the migration.

To review retrieved forms:

1. From the project page, click Forms.

   The Forms page appears, showing the status of the objects ready for migration:

   • Access Form - The name of the original Access Form.

   • Source type:

     • Table - The Oracle table that was migrated from the Microsoft Access table.

     • Query - The Oracle view that was migrated from the Microsoft Access query.

     • SQL Query - The original Microsoft Access SQL query that the Microsoft Access form is based on. Note that this query has not been parsed. Therefore, you must edit it to make it valid Oracle SQL syntax.

     • Nothing - The form has no underlying source type.

   • Source Name - The Oracle table or view name if the source type is a table or query.

   • Status - Form status as Valid or Invalid. The source of the form must have a status of Valid before you can select it for migration.

     A form's status is based on two factors: status of its underlying source object and inclusion of the source object in the migration. Specifically, a form has a status of valid if either one of these situations exists:

     • Its Source Type object (table, query, or SQL query) is valid, and it has been included in the migration. Its check box is enabled and can be selected.

     • Its Source type object has a status of valid, but the source object was not included in the migration. Its check box is disabled.

     A form has a status of invalid if either one of these situations exists:

     • No Source Type is listed. Its check box is disabled.

     • Its Source Type object (table, query, or SQL query) is invalid. Its check box is disabled.

   • Startup Form - Identifies the form that displays when you open your Microsoft Access database.

   • Parent Form - Indicates the form and subform relationship that exists in your Microsoft Access database. For example, the CallListSub form shows Contacts as its Parent Form.

   • Migrate To: Form, Tabular Form, or Report and Form - The select list appears if the source type is a valid table.

2. To run a bulk process that attempts to compile all invalid SQL queries, click Attempt to compile invalid SQL queries at the bottom of the page.

   Using this option can validate some SQL queries that show a status of invalid. Note that SQL queries from Microsoft Access forms are not loaded into the Exporter tool, and are therefore not parsed.

3. To edit a SQL query:

   1. On the Forms page, click the SQL Query you want to edit.

   2. Click Validate to find the invalid part of the SQL query.

   3. Update the query and validate it.

   4. When it is validated, click the project name breadcrumb.

   5. To include the validated query, click Forms on the project page to go to the Forms page. Then select the newly validated query in the left column and click Apply Changes.

4. To edit a query:

   1. On the Forms page, click Query for the form you want to edit.

   2. Click Compile to find the invalid part of the query.

   3. Click Access Query to review the initial query and compare it to the converted query.

   4. Update the query and recompile it.

   5. When it is validated, click the Queries breadcrumb.

   6. To include this validated query, select it in the left column on the Queries page and click Apply Changes.

5. To review details about a form, click the link in the Access Form column.

6. To include forms in the migration, select the Include check box in left column on the Forms page.

7. Click Apply Changes to save your selections.

3.6.5 Reviewing Retrieved Reports

The Application Migration identifies invalid reports and lists additional information, such as the report's source type and source name.

After you update the reports, select the ones you want to include in the migration. To include a report, the source of the report must have a status of Valid.

To review retrieved reports:

1. From the project page, click Reports.

   The Reports page appears, showing the status of the objects ready for migration:

   • Access Report column

   • Source name

   • Source type - Options include:

     • Table

     • Query - The Oracle view that was migrated from the Microsoft Access query.

     • SQL Query - The original Microsoft Access SQL query that the Microsoft Access form is based on. Note that this query has not been parsed. Therefore, you must edit it to make it valid Oracle SQL syntax.

     • Nothing - The report has no underlying source type.

   • Status - Valid or Invalid. The source of the report must have a status of Valid before you can select it for migration.

     A report status is based on two factors: status of its underlying source object and inclusion of the source object in the migration. Specifically, a report has a status of valid if either one of these situations exists:

     • Its Source Type object (table, query, or SQL query) is valid, and it has been included in the migration. Its check box is enabled and can be selected.

     • Its Source type object has a status of valid, but the source object was not included in the migration. Its check box is disabled.

     A report has a status of invalid if either one of these situations exists:

     • No Source Type is listed. Its check box is disabled.

     • Its Source Type object (table, query, or SQL query) is invalid. Its check box is disabled.

2. To run a bulk process that attempts to compile all invalid SQL queries, click Attempt to compile invalid SQL queries at the bottom of the page.

   Using this option can validate some SQL queries that show a status of invalid. Note that SQL queries from Microsoft Access forms are not loaded into the Exporter tool and are therefore not parsed.

3. To edit a SQL query:

   1. On the Reports page, click the SQL Query link you want to edit.

   2. Click Validate to find the invalid part of the SQL query.

   3. Update the query and validate it.

   4. When it is validated, click the project name breadcrumb.

   5. To include the validated query, click Reports on the project page to go to the Reports page. Then select the newly validated SQL query in the left column and click Apply Changes.

4. To edit a query:

   1. On the Reports page, click Query for the report you want to edit.

   2. Click Compile to find the invalid part of the query.

   3. Click Access Query to review the initial query and compare it to the converted query.

   4. Update the query and recompile it.

   5. When it is validated, click the Queries breadcrumb.

   6. To include this validated query, select it in the left column on the Queries page and click Apply Changes.

5. To review details about a report, click the link in the Access Report column.

6. To include reports in the migration, select the Include check box in left column on the Reports page.

7. Click Apply Changes to save your selections.

3.6.6 Reviewing Database, Module, and Pages Information

From the project page, you can drill down to see information about the modules, pages, and the database for the migration project.

• Modules - Under Objects in the left column, click Modules. Selecting a module displays the Visual Basic Code, enabling you to extract embedded SQL statements for you to use or edit in your Oracle Application Express application.

• Pages - Under Objects in the left column, click Pages. Displays information for reference purposes.

• Database - Under Database in the far right column, click the database name to view summary information about the Microsoft Access database, including the full path and size of the .mdb file.

3.7.1 About Generating the Oracle Application Express Application

When you create an application, a home page is defined by default. You have the option to create additional blank pages so that you can introduce further navigation possibilities. You can then choose which user interface theme your application should be based on. By default, the application uses one level of tabs.

3.7.2 Setting Up Application Defaults

As a shortcut, you can also set some application defaults. These defaults are used whenever you create new applications.

To set up application defaults (optional):

1. On the right side of the project page under Tasks, click Set Application Defaults.

2. Select the options you want to use as defaults.

   To learn more about a specific attribute, see field-level help.

3. Click Apply Changes.

   The project page appears.

3.7.3 Generating an Application

You can generate an application based on valid forms and reports, or a maintenance application based on valid tables and views.

To generate an application:

1. On Application Migrations page, click the project name.

2. Click Create Application.

   The Generate Application wizard appears.

3. On Generate Application:

   1. Name - Enter a name to identify the application to developers. The default value is taken from the migration project name.

   2. Create Application - Select a method:

      • Based on Migration Project - Select this option to manually enter all pages.

      • Based on existing application design model - Select this option to reuse an existing application definition.

   3. Click Next.

4. On Pages:

   1. To add a blank page to the application, click Add Page.

      The new page appears in the list at the top of the page.

   2. For Subordinate to Page, select a subordinate page.

   3. For Page Name, enter a page name.

   4. Click Next.

5. For User Interface:

   1. For Select a theme - Note the selected theme.

   2. Show - To select another theme, make a new selection. From Select a theme, select a new theme.

   3. Click Next.

6. Confirm your selections and click Create.

   The Application home page appears.

7. To preview the application, click Run Application.

8. Sign in using your Oracle Application Express workspace credentials.

   Your application now appears as a separate application in Oracle Application Express.

9. To customize application attributes:

   1. On the Runtime Developer toobar, click the Application ID.

      The Application home page appears.

   2. To edit application attributes including the application name, click Edit Application Properties.

      The Edit Application Definition page appears.

   3. Edit the attributes on the Definition, Security, Globalization, and User Interface pages.

   See Also:

   "Managing Application Attributes" in Oracle Application Express Application Builder User's Guide