2 Migration Process

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

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 database file format (.mdb for Access 2003 and earlier releases, .accdb for later releases). Figure 2-1 illustrates the Microsoft Access architecture:

Figure 2-1 Microsoft Access Architecture

Description of Figure 2-1 follows
Description of "Figure 2-1 Microsoft Access Architecture"

In a single-user Microsoft Access application, the database file and Microsoft Access are located on the same system. In a multi-user Microsoft Access application, the database 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 UPDATEs 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. See Oracle Database Concepts for a description of the Oracle architecture. You can also see the following:

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 follows
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 native database 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:

    Linked tables (to other Microsoft Access databases) can be migrated. SQL Developer 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 database file as the user name for the destination Oracle user. If you create an Oracle user in this way, the password is ORACLE.

To ensure that SQL Developer 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 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-3.

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

Description of Figure 2-3 follows
Description of "Figure 2-3 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.7 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:

2.7.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.7.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.7.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.