Release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT
Part Number A97262-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle Migration Workbench Reference Guide for Microsoft Access 2.0, 95, 97, 2000 Migrations Release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT Part Number A97262-01 |
|
This chapter introduces the migration process by outlining the architecture of both Microsoft Access and Oracle. It includes the following sections:
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 Microsoft Jet database engine to manage data stored in the native .MDB file format. The following diagram illustrates the Microsoft Access architecture:
Text description of the illustration 2_1.gif
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 the 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. The Microsoft Access 2.0 DAO DLL is DA02016.DLL and the Microsoft Access 95 DAO DLL is DAO350.DLL.
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.
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.
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 motified the data before it posts the changes.
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.
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.
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.
Oracle9i, Oracle8i, and Oracle8 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 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.
The Migration Workbench also converts certain Microsoft Access field validation rules to stored procedures within Oracle. For more information, see the Microsoft Access Functions topic.
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.
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.
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.
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.
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.
The following diagram illustrates that Microsoft Access requires ODBC to make its connection to Oracle.
Text description of the illustration 2_2.gif
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 on tables in Oracle in order to support dynasets against those tables. If a remote table does not have a primary key, 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. When these tables are migrated to Oracle, they do not have a primary key 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 before you migrate or that you define a primary key once the tables are migrated to Oracle. Any tables that can be updated, but do not have primary keys are flagged with a warning in the Log window.
It is recommended that you complete the steps below before you run the Capture Wizard. To prepare the Microsoft Access database for capture:
The Migration Workbench 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 Migration Workbench Exporter for Microsoft Access application, you may receive an error message if the database is secured. By default, the Migration Workbench 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 Oracle Migration Workbench Exporter for Microsoft Access.
To copy the contents of the secured database into a new database:
When you migrate an 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 an 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.
Text description of the illustration 2_3.gif
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.
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 Microsoft Access exits. Because of this connection caching, any attempt to connect to a linked table will 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.
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. 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.
In addition, if the application grows, you can move the Oracle server to larger computers without changing the application.
Text description of the illustration 2_4.gif
The Oracle Objects for OLE and OLEDB/ADO Cookbook provides additional information about how to extend the application. You can find this document on the Oracle Technology Network (OTN) Web site at:
http://otn.oracle.com/tech/migration/workbench
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:
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.
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.
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.
|
 Copyright © 1998, 2002 Oracle Corporation. All Rights Reserved. |
|
