Release 1.2.5.0.0 for Windows
Z26073-02
 Library |
 Product |
 Contents |
 Index |
| Oracle Migration Workbench for MS Access Reference Guide Release 1.2.5.0.0 for Windows Z26073-02 |
|
This chapter introduces the migration process by outlining the architecture of both MS Access and Oracle. It includes the following sections:
MS Access is based on a file server DBMS technology named "Jet." Forms, reports, and Basic code in MS Access rely on Jet to manage data stored in the native "mdb" file format. This is illustrated in the following diagram:
In a single-user MS Access application, the mdb file and MS Access are located on the same machine. In a multi-user MS Access application, the mdb file is placed on a file server and shared. Each client runs a copy of MS Access and the Jet engine. In this configuration, Jet 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 MS Access Basic and Visual Basic. The MS Access 2.0 DAO DLL is DA02016.DLL and the MS Access 95 DAO DLL is DAO350.DLL. DAOs define and expose databases, workspaces, query definitions, parameters, recordsets, tables, fields, indexes, relationships, users, and groups from Jet.
Jet 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 MS Access. Jet also supports a link to ODBC, which allows access to a wide range of DBMS servers.
When Jet processes a query, it returns a recordset (a cursor) for the resulting set of records. Jet supports two types of recordsets; snapshots and dynasets.
A snapshot is a picture of data as it existed at the time the query was run. When returning a snapshot, Jet 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 and it does not reflect changes made by other users after it has been opened.
A dynaset is a live view of the data. When returning a dynaset, Jet extracts the key values from the data and stores them in memory. When a user requests rows of data from the dynaset, Jet 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 is started will not be a part of the set of key values and will not be 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, they are marked #DELETED# when presented 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 at will. When used with local data, dynasets are fast and effective. However, the dynaset model presents one of the key performance challenges when MS Access works with an RDBMS server such as Oracle.
Jet handles updates by multiple users by using optimistic and pessimistic locks.
Using pessimistic locking, Jet 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.
Jet employs an optimistic locking scheme when working with Oracle. An optimistic locking scheme does not place a hard lock on the source table(s). Instead, when a change is to be committed, Jet checks to make sure that the data has not been altered by another user before allowing the changes to be posted.
Jet 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 Jet query processor does not support a full implementation of SQL. It optimizes queries, especially when the query references both local and remote tables. Jet 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. Jet relies on the transaction support of any RDBMS attached via ODBC.
Jet 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 related units of work, developers use transactions to improve performance. If a program makes numerous references to a table, grouping the work in a single transaction will force Jet to perform the operation in memory and then commit all work to disk when the transaction is committed. Be aware that this type of transaction may not map directly to Oracle.
Oracle8 and Oracle8i are powerful, flexible, and scaleable relational database management system (RDBMS) servers, which run on a range of computer systems, from personal computers to the largest mainframes. Oracle has been 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 versioning (not page locking as provided by MS 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 and are focused on the elements that pertain to working with MS Access. Refer to the following Oracle Server manuals for a complete description of the Oracle architecture. These manuals can also be found in online format on CD-ROM:
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 a table as a whole. You can also set a trigger to run before or after the event. For example, you can set a trigger to run after any 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 MS Access with Oracle, triggers and stored procedures play a role in mapping the functionality of MS Access to Oracle, such as in the support for the MS Access AUTONUMBER (COUNTER) data type in Oracle.
The PL/SQL Programming Language is an ALGOL-based language like 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 Oracle's client-side tools 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 might 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 MS Access.
Unlike MS 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.
MS Access developers use transactions to improve the performance of Jet. Grouping database statements in a transaction forces Jet to attempt to complete all database work in memory; Jet defers writing to disk until the transaction is committed. When this use of transactions is mapped to Oracle via Open Database Connectivity (ODBC), Jet sends only the outer most pair of Begin/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 MS Access to Oracle.
A database administrator has great flexibility when configuring Oracle. Data can be written on multiple disks for increased performance, rollback and recovery options can be tuned, and computer resources can be allocated to optimize the configuration for each server. Oracle also supports distributed processing, so data can be distributed across multiple machines. 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 MS 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 complete tables). Jet technology is focused on single-user performance with adequate multi-user capabilities; Oracle on the other hand 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 MS Access and Oracle requires an understanding of how Jet works with centralized servers.
The following diagram illustrates that MS Access requires ODBC to make its connection to Oracle.
ODBC is an API that allows client applications to connect to different RDBMS servers. Jet has been designed to make efficient use of ODBC while requiring a level 1 ODBC driver. When MS Access uses ODBC to connect to remote RDBMS servers, Jet continues to function as the DBMS engine for MS Access. MS Access forms, reports, and Basic code continue to work with Jet as if they were working with local or shared data in the mdb file format. Jet presents remote Oracle tables as attached tables. These attached tables are created at design time and appear to be local tables.
Jet 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, Jet only opens a non-updateable snapshot on the table.
It is possible to define updateable tables in MS Access that do not have a primary key. When these tables are migrated to Oracle, they do not have a primary key and MS Access is unable to update them. If you need the migrated tables to be updateable, you should ensure that either all updateable MS Access tables have primary keys before you migrate or that you define a primary key once the tables are migrated to Oracle. Any updateable tables that do not have primary keys are flagged with a warning in the Log Window.
Follow the steps outlined below to prepare your MS Access database for migration. It is imperative that you complete these steps before you run the Capture Source Wizard:
When you migrate an MS Access database to Oracle, some changes are required to enable your MS Access forms and reports to continue accessing the data. You must select to have your MS Access database modified in order for these changes to be made.
Figure 2-3 shows an MS Access application before and after migration to Oracle. Both before 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 this table.
After migration, app.mdb has two attached tables for each original table and a mapping query. The original MS 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 mapping query called <tablename> is created. This mapping query takes the place of the original table in the application. The mapping query can refer to either <tablename>_L or <tablename>_R; you can switch between the local and remote table as you move your application to Oracle. The mapping query also helps resolve reserved word conflicts between MS Access and Oracle. For example, a column named "Sequence" must be renamed as this is an Oracle reserved word. The mapping query can remap this Oracle column back to Sequence for use by the MS Access application. Refer to Chapter 3 for a complete list of Oracle Reserved Words.
All requests for data from the MS Access application are directed to the mapping query, which retrieves the data from the new Oracle table.
The original table that has been exported to Oracle is retained in case the data is needed locally and, more importantly, so that any new MS Access forms created can be based on this table. This enables a complete set of table properties to be inherited by the MS Access form. After the MS Access form is defined, the data source can be switched to the mapping query so that the data can be retrieved from Oracle.
If the application opens a table directly (not using a dynaset or snapshot) it will not work with linked tables. This restriction also applies after you move the data to Oracle. If this happens, you might 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.
After you move the data management portion of your MS Access application to Oracle, you can rely on Oracle to protect your data and maintain all referential integrity and business rules that you have encoded in PL/SQL.
With this foundation, you can extend your application with MS 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 your application grows, you can move your Oracle server to larger computers without changing your application.
The Oracle Objects for OLE and OLEDB/ADO Cookbook provides additional information about how to extend your application. You can find this document on the Oracle Technology Network (OTN) Web site at the following URL:
|
 Copyright © 2000 Oracle Corporation. All Rights Reserved. |
|