7/13

Beta Draft

# 2 Migration Process

This chapter introduces the migration process by outlining the architecture of both Microsoft Access and Oracle. It includes the following sections:

- Overview of the Microsoft Access Architecture

- Overview of the Oracle Architecture

- Comparing Microsoft Jet Database Engine, ODBC, and Oracle Architecture

- Preparing the Microsoft Access Database for Capture

- Turning Off Security Settings in Microsoft Access

- Using Oracle as the Back-End

## 2.1 Overview of the Microsoft Access Architecture

Microsoft Access is based on a file server DBMS technology named Microsoft Jet database engine. Forms, reports, and Basic code in Microsoft Access rely on the Microsoft Jet database engine to manage data stored in the native .MDB file format. Figure 2-1 illustrates the Microsoft Access architecture:

Figure 2-1 Microsoft Access Architecture

Description of "Figure 2-1 Microsoft Access Architecture"

In a single-user Microsoft Access application, the .MDB file and Microsoft Access are located on the same system. In a multi-user Microsoft Access application, the .MDB file is placed on a file server and shared. Each client runs a copy of Microsoft Access and Microsoft Jet database engine. In this configuration, Microsoft Jet database engine must move a large amount of data across the network, including whole tables, to complete its query processing.

The Data Access Objects (DAOs) DLL provides a hierarchy of classes to Microsoft Access Basic and Visual Basic. DAOs define and expose databases, workspaces, query definitions, parameters, recordsets, tables, fields, indexes, relationships, users, and groups from Microsoft Jet database engine.

Microsoft Jet database engine manages links to external data sources. Links to dBase, Paradox, and Btrieve are made through an internal Jet ISAM driver interface. These DLLs are included with Microsoft Access. Microsoft Jet database engine also supports a link to ODBC that allows access to a wide range of DBMS servers.

### 2.1.1 Microsoft Jet Database Engine Recordsets

When Microsoft Jet database engine processes a query, it returns a recordset (a cursor) for the resulting set of records. Microsoft Jet database engine supports snapshots and dynasets types of recordsets.

A snapshot is a picture of data as it existed at the time the query was run. When returning a snapshot, Microsoft Jet database engine runs the query to completion, extracts all of the resulting rows and columns into a virtual table, and presents this virtual table to the user. The user of a snapshot can perform a full range of operations on a snapshot, such as query the snapshot and base forms and reports on the snapshot. You cannot make changes to snapshot data. The snapshot data does not reflect changes made by other users after you have opened it.

A dynaset is a live view of the data. When returning a dynaset, Microsoft Jet database engine extracts the key values from the data and stores them in memory. When a user requests rows of data from the dynaset, Microsoft Jet database engine fetches the rows of interest by looking them up in the base tables via the internally stored key values. Once you open a dynaset, the set of key values cannot change. Thus, while the data pointed to by the key value may change and is reflected to the user, new rows added after the query begins is not part of the set of key values and is not made visible to the user. Rows that you delete after you run the keyset query are still part of the set of key values. However, these rows are marked `#DELETED#` when displayed to the user.

The dynaset model is a powerful and flexible model that gives the user of PC-based information the opportunity to browse large quantities of data and update the data. When used with local data, dynasets are fast and effective. However, the dynaset model presents one of the key performance challenges when Microsoft Access works with an RDBMS server, such as Oracle.

### 2.1.2 Microsoft Jet Database Engine Multi-User Updates

Microsoft Jet database engine handles updates by multiple users by using optimistic and pessimistic locks.

Using pessimistic locking, Microsoft Jet database engine places a hard lock on the data page that contains the row being edited. Other users cannot start editing the locked row until the lock is abandoned or the changes are written to disk.

Microsoft Jet database engine employs an optimistic locking scheme when working with Oracle. An optimistic locking scheme does not place a hard lock on the source tables. Instead, when you want to commit a change, Microsoft Jet database engine checks to make sure that another user has not modified the data before it posts the changes.

### 2.1.3 Microsoft Jet Database Engine Enforced Referential Integrity

Microsoft Jet database engine supports declarative referential integrity. This includes primary key/foreign key relationships with one-to-one and one-to-many cardinality with cascading `UPDATE`s and `DELETES`.

### 2.1.4 Microsoft Jet Database Engine Query Processor

The Microsoft Jet database engine query processor does not support a full implementation of SQL. It optimizes queries, especially when the query references both local tables and remote tables. Microsoft Jet database engine can connect to a wide range of data sources and process queries against all of them. Transaction support is limited to native file format database tables. Microsoft Jet database engine relies on the transaction support of any RDBMS attached via ODBC.

### 2.1.5 Microsoft Jet Database Engine Transactions

Microsoft Jet database engine supports an explicit transaction model. Transactions are not started until a `BeginTrans` statement is executed. Transactions are committed with `CommitTrans` and aborted with `Rollback`. In addition to using transactions to group units of work, you can use transactions to improve performance. If a program makes numerous references to a table, grouping the work in a single transaction forces Microsoft Jet database engine to perform the operation in memory. It then commits all work to disk when the transaction is committed. However, this type of transaction may not map directly to Oracle.

## 2.2 Overview of the Oracle Architecture

Oracle databases are powerful, flexible, and scalable relational database management system (RDBMS) servers that run on a range of computer systems, from personal computers to largest mainframes. Oracle is designed to run effectively in a client/server environment and supports hundreds to thousands of users.

The Oracle architecture supports advanced server features, such as record locking with version references, (not page locking as provided by Microsoft Access), advanced query optimization, the PL/SQL programming language, data replication, distributed database management, and other important features.

The architectural features discussed here are only a few of the features found in Oracle. These features are focused on the elements that pertain to working with Microsoft Access. Refer to the Oracle Server guides for a complete description of the Oracle architecture. You can also view these guides in online format on CD-ROM. They are:

- Oracle Database Concepts

- Oracle Database Administrator's Guide

- PL/SQL User's Guide and Reference

- Oracle Database Error Messages

### 2.2.1 Triggers and Stored Procedures

Oracle allows you to write and store code in the DBMS along with data. You can associate trigger code with an `UPDATE`, `INSERT`, or `DELETE` event for each row or for an entire table. You can also set a trigger to run before an event or after an event. For example, you can set a trigger to run after a row is updated.

A stored procedure is a general routine, either function or subroutine, that is stored in pre-compiled form on the server. A trigger may call stored procedures, but triggers are only activated by specific database activity, such as the insertion of a row in a table.

When using Microsoft Access with Oracle, triggers and stored procedures play a role in mapping the functionality of Microsoft Access to Oracle, such as in the support for the Microsoft Access `AUTONUMBER` (`COUNTER`) data type in Oracle.

SQL Developer also converts certain Microsoft Access field validation rules to stored procedures within Oracle.

### 2.2.2 PL/SQL Programming Language

The PL/SQL Programming Language is an ALGOL-based language, similar to Pascal. PL/SQL is a modern, full-featured programming language with exception handling. You can use PL/SQL to write stored programs and triggers in Oracle. It is also the programming language used in many of the client-side tools of Oracle, such as Forms from the Oracle Developer suite of products.

### 2.2.3 Sequences

A sequence is a unique number generator that is implemented in shared memory on a server. It is designed to provide a set of unique values for PL/SQL programs for use as primary keys. Sequences are designed for high performance applications that may otherwise single-thread on table-based unique number generators. You use sequences, along with supporting code in a trigger, to emulate the `COUNTER` field type in Microsoft Access.

### 2.2.4 Transactions

Unlike Microsoft Access, Oracle supports an implicit transaction model. Each SQL statement is part of a logical transaction. A logical transaction begins with the first SQL statement and ends with a `Commit` or `Rollback` statement. Immediately after either of these statements, a new transaction takes effect with the next SQL statement.

Microsoft Access developers use transactions to improve the performance of Microsoft Jet database engine. Grouping database statements in a transaction forces Microsoft Jet database engine to attempt to complete all database work in memory. Microsoft Jet database engine defers writing to disk until the transaction is committed. When this use of transactions is mapped to Oracle through the Open Database Connectivity (ODBC), Microsoft Jet database engine sends only the outer most pair of `Begin` or `Commit` transaction requests. Oracle keeps an open transaction during the entire processing period. You must decide if you want this outcome when you move from Microsoft Access to Oracle.

### 2.2.5 Database Users

For each migrated Microsoft Access database, two database users are created in the Oracle Model:

- A user with the same name as the Microsoft Access database unique name. This user owns the Oracle objects that correspond to user-created objects in the Microsoft Access database.

- A user named `OMWB_EMULATION`. This user owns Oracle objects (such as packages, stored procedures, and types) that emulate a limited subset of Microsoft Access functions. (To see the emulation of these functions, expand the omwb_emulation user tree in the Oracle Model hierarchy.)

### 2.2.6 Other Oracle Features

A database administrator has great flexibility when configuring Oracle. You can write data on multiple disks for increased performance. You can tune rollback and recovery options. You can allocate computer resources to optimize the configuration for each server. Oracle also supports distributed processing, so you can distribute data across multiple systems. Oracle offers a version of the server called Trusted Oracle Server for applications that require a higher level of user and use authentication.

## 2.3 Comparing Microsoft Jet Database Engine, ODBC, and Oracle Architecture

Using Oracle with Microsoft Access can increase the robustness and reliability of a multi-user system. This reduces network traffic because only query requests and the resulting data are sent over the network, instead of entire tables. Microsoft Jet database engine technology is focused on single-user performance with adequate multi-user capabilities. Oracle is a mature central server technology focused on multi-user performance, rollback and recovery, and centralized query processing.

Obtaining adequate performance from the combination of Microsoft Access and Oracle requires an understanding of how Microsoft Jet database engine works with centralized servers.

Figure 2-2 illustrates that Microsoft Access requires ODBC to make its connection to Oracle.

Figure 2-2 Microsoft Access/ODBC/Oracle Architecture

Description of "Figure 2-2 Microsoft Access/ODBC/Oracle Architecture"

ODBC is an API that allows client applications to connect to different RDBMS servers. Microsoft Jet database engine has been designed to make efficient use of ODBC while requiring a level 1 ODBC driver. When Microsoft Access uses ODBC to connect to remote RDBMS servers, Microsoft Jet database engine continues to function as the DBMS engine for Microsoft Access. Microsoft Access forms, reports, and Basic code continue to work with Microsoft Jet database engine as if they were working with local or shared data in the .MDB file format. Microsoft Jet database engine presents remote Oracle tables as attached tables. These attached tables are created at design time and appear to be local tables.

Microsoft Jet database engine requires a primary key or unique index on tables in Oracle in order to support dynasets against those tables. If a remote table does not have a primary key or unique index, Microsoft Jet database engine only opens a snapshot on the table that cannot be updated.

It is possible to define tables in Microsoft Access that you can update, but do not have a primary key or unique index. When these tables are migrated to Oracle, they do not have a primary key or unique index and Microsoft Access is unable to update them. If you need to update the migrated tables, you should ensure that either all Microsoft Access tables that you can update have primary keys or unique indexes before you migrate or that you define a primary key or unique index once the tables are migrated to Oracle. Any tables that can be updated, but do not have primary keys or unique indexes, are flagged with a warning in the Log window.

## 2.4 Preparing the Microsoft Access Database for Capture

It is recommended that you complete the steps in this section before you capture the source database. In addition, if any table or field names in the Microsoft Access database contain spaces or are longer than 30 characters, see Table and Field Name Considerations later in this section. To prepare the Microsoft Access database for capture:

1. Make a back up of the Microsoft Access database files.

2. Turn off security. For more information about security, see Turning Off Security Settings in Microsoft Access.

3. If the application contains linked tables to other Microsoft Access databases, refresh these links by opening the application in the Microsoft Access IDE and choosing Tools -> Add Ins ->Linked Table Manager.

   Note:

   The Migration Workbench supports linked tables to other Microsoft Access databases. The Migration Workbench automatically creates a user schema within the Oracle database for each Microsoft Access database migrated. However, Oracle recommends that you move all tables into a single Microsoft Access database in order to ensure that a single user schema is created in the Oracle database.

4. Compact the Microsoft Access database files by choosing Tools -> Database -> Compact Database.

   Note:

   Ensure that the database is not a replica database. SQL Developer cannot migrate a replica Microsoft Access database.

### 2.4.1 Table and Field Name Considerations

The following considerations apply for Microsoft Access database table names and field names that contain spaces or that are longer than 30 characters:

- Any spaces in the name are replaced by underscores in the corresponding Oracle object. For example, if a field name in a Microsoft Access table is Transaction ID, the column name in the Oracle table as a result of the migration is Transaction_ID.

- Any names longer than 30 characters are truncated to 30 characters in the corresponding Oracle object. If necessary, the last character or characters in the names are set to ensure that the Oracle table and column names after the migration will be unique.

In addition, if the Microsoft Access table or field name conflicts with an Oracle reserved word, an underscore is appended to the Oracle table or column name, as explained in Oracle Reserved Words in Chapter 3.

## 2.5 Turning Off Security Settings in Microsoft Access

SQL Developer does not support the migration of Microsoft Access databases that have security enabled. When you attempt to export the Microsoft Access database to XML using the Exporter for Microsoft Access application, you may receive an error message if the database is secured. By default, SQL Developer uses the name of the Microsoft Access MDB file as the user name for the destination Oracle user. If you create an Oracle user in this way, the password is `ORACLE`.

In order to ensure that the Migration Workbench can migrate the Microsoft Access table data, it is necessary to copy the contents of the secured database into a new database. Everything is copied over to the new database, except for the security settings. You can then export the new Microsoft Access database to an XML file using the Exporter tool.

To copy the contents of the secured database into a new database:

1. In Microsoft Access, choose File -> New Database.

2. Select the Blank Database icon and click OK.

3. Specify the file name and location of the new database and click Create.

4. From within the new database, choose File -> Get External Data -> Import.

5. Select the secured Microsoft Access database you want to import and click Import.

6. From the Import Objects dialog, click Options. Ensure that the Relationships and Definition and Data options are selected.

7. From the Tables tab, choose Select All.

8. Click OK.

9. Export the new Microsoft Access database to an XML file using the Exporter tool. If the database migrates successfully, the issue was due to a secured database.

## 2.6 Modifying the Microsoft Access Database

When you migrate a Microsoft Access database to Oracle, you must make some changes to allow the Microsoft Access forms and reports to continue to operate against the migrated data residing in the Oracle database. From the Migration Workbench, choose Action -> Modify Access Database to enable the Migration Workbench to make the necessary modification.

Figure 2-3 shows a Microsoft Access application before migration to Oracle and after migration to Oracle. Both before migration and after migration, app.mdb contains the forms, reports, macros, and Basic modules that make up the application. Before migration, app.mdb contains an attached table from data.mdb. This diagram uses <tablename> to refer to the name of the attached table.

Figure 2-3 Microsoft Access Application Before Migration and After Migration

Description of "Figure 2-3 Microsoft Access Application Before Migration and After Migration"

After migration, app.mdb has two attached tables for each original table and query. The original Microsoft Access tables are renamed to <tablename>_L. The original table is exported to Oracle where it is called <tablename>. A table attachment is created to the Oracle table with the name <tablename>_R.

Because the forms, reports, and modules in app.mdb are expecting a table with the original table name, a query called <tablename> is created. This query takes the place of the original table in the application. The query can refer to either <tablename>_L or <tablename>_R. You can switch between the local and remote table as you move the application to Oracle. The query also helps resolve reserved word conflicts between Microsoft Access and Oracle. For example, you must rename the sequence column because it is an Oracle reserved word. The query can remap this Oracle column back to sequence for use by the Microsoft Access application. For a complete list, refer to the Oracle Reserved Words topic.

All requests for data from Microsoft Access forms and reports are directed to the query. This query references the generated ODBC linked table that retrieves the data from the Oracle database.

The original table that has been exported to Oracle is retained in case the data is needed locally. It is also retained so that any new Microsoft Access forms that you create can be based on this table. This enables the Microsoft Access forms to inherit a complete set of table properties. After the Microsoft Access form is defined, you can switch the data source to the query so that Oracle can retrieve the data.

If the application opens a table directly (not using a dynaset or snapshot), it does not work with linked tables. This restriction also applies after you move the data to Oracle. If this happens, you may want to leave some tables in the app.mdb file so that each client has an independent copy. This could be appropriate for tables with lookup values, such as a State table. If you must move a table that is opened directly to the data.mdb file, you must change the application to use dynasets or snapshots.

### 2.6.1 ODBC Connection Issues with Linked Tables

When logging on to an Oracle Database through Microsoft Access the following Microsoft Jet database engine behavior should be considered.

When you connect to an Oracle server, the Microsoft Jet database engine caches the user name and password entered until you exit Microsoft Access. The connection caching causes any attempt to connect to a linked table to fail, unless the user that corresponds to the cached user name has access privileges to that linked table. Connecting as a user that has system privileges or select privileges to all the tables ensures that all the tables are accessed from Microsoft Access.

Refer to the Managing User Privileges and Roles section in the Oracle9i Database Administrators guide for further details.

## 2.7 Extending the Application

After you move the data management portion of the Microsoft Access application to Oracle, you can rely on Oracle to protect the data and maintain all referential integrity and business rules that you have encoded in PL/SQL.

With this foundation, you can extend the application with Microsoft Access or a wide range of other tools, as shown in Figure 2-4.

Figure 2-4 Extending the Application with a Mix of Client Tools

Description of "Figure 2-4 Extending the Application with a Mix of Client Tools"

In addition, if the application grows, you can move the Oracle server to larger computers without changing the application.

Oracle offers several high productivity tools such as Oracle Web DB, Oracle Developer, and Oracle Objects for OLE. Oracle Objects for OLE (OO4O) is a high performance connectivity solution for Visual Basic, Delphi, and other client tools that can control OLE Automation Servers. For information about these tools, see the Oracle Technology Network (OTN) Web site at:

`http://www.oracle.com/technology`

## 2.8 Using Oracle as the Back-End

You may notice some differences in behavior when using an Oracle database in conjunction with a Microsoft Access application. The differences are described in the following sections:

- Autonumber Options

- Default Options

- Multiple Forms Accessing the Same Record

### 2.8.1 Autonumber Options

When using a Microsoft Access table that has an `AUTONUMBER` column, the value of the `AUTONUMBER` is displayed once you start entering data for the record. Conversely, when you are using an ODBC link table referencing an Oracle table, the `AUTONUMBER` value displays after you have committed the record.

### 2.8.2 Default Options

When using a Microsoft Access table that contains defaults, the value of the default is displayed prior to entering data for the record. Conversely, when you are using an ODBC link table referencing an Oracle table, the default value displays after you have committed the record.

### 2.8.3 Multiple Forms Accessing the Same Record

When using local Microsoft Access tables, if two forms are accessing the same record, changes made in one form are immediately reflected in the other form. However, when you are using an ODBC link table referencing an Oracle table, modifications made in one form are reflected in the other form once you refresh the application by choosing the Records -> Refresh option.