Migrating DBMS Management
from Microsoft Access Applications
to Oracle 7.1 RDBMS Server
White Paper
Oracle Corporation, Inc.
December, 1995

Contents

INTRODUCTION
ASSUMPTIONS
TOPICS COVERED
THE ACCESS/ORACLE CLIENT–SERVER ARCHITECTURE
ACCESS ARCHITECTURE
JET/ODBC/ORACLE ARCHITECTURE
MIGRATING ACCESS APPLICATIONS TO ORACLE RDBMS SERVER
OVERVIEW
MIGRATION IN DETAIL
MIGRATION, STEP BY STEP
EXTENDING YOUR APPLICATION
APPENDICES 9
APPENDIX 1 - ORACLE RESERVED WORDS
APPENDIX 2 - CODE AND QUERY SAMPLES
APPENDIX 3 - JET ARCHITECTURE DETAILS
APPENDIX 4 - ORACLE 7.1 RDBMS ARCHITECTURE DETAILS
OTHER ORACLE FEATURES
APPENDIX 5 - ODBC ARCHITECTURE DETAILS
APPENDIX 6 - JET/ODBC/ORACLE ARCHITECTURE DETAILS
APPENDIX 7 - ACCESS / ORACLE TUNING AND CUSTOMIZATION
APPENDIX 9 - QUESTIONS AND ANSWERS (PRELIM)

Introduction
Accurate and timely data is essential for business operations in the 1990’s. Faster computers along with easy-to-use database software has led to the creation of personal and departmental databases which manage important business data. Products such as Microsoft Access allow developers and advanced users to build complete business systems. Microsoft Access is based on file sharing technology. Because of this, Access lacks the speed, reliability and robustness provided by an independent RDBMS server.

Using an RDBMS server with Access in a client–server architecture uses the strengths of both technologies. Access provides excellent form and report systems, as well as a complete programming language (Access Basic). An independent relation database management system (RDBMS) provides reliable, robust, and secure high speed data management.
Oracle 7.1 RDBMS Server, from Oracle Corporation, is a modern, scaleable, high performance database server which can run on wide range of computers from PCs to mainframes. Oracle 7.1 operates in a networked, client–server environment and can support tens, hundreds or thousands of users depending on the server computer.
This paper, "Migrating DBMS Management from Microsoft Access Applications to Oracle 7.1 RDBMS Server", explains how to use Microsoft Access and Oracle 7.1 together in a client–server architecture. If you have an investment in Access applications, you can retain this investment while adding the advanced features of Oracle 7.1 RDBMS to you application architecture.
After reading this paper, you will know how to move a working Access application to an Access / Oracle architecture. In addition, you will know how to extend your application using Access or other client development tools such as Oracle Power Object or Oracle’s Developer/2000 tool set.
The subjects covered in this paper also apply to Microsoft Visual Basic (VB) applications which use the Jet DBMS engine. Visual Basic applications can be moved to Oracle as easily as Access Applications.

Assumptions
This paper assumes that you are familiar with Microsoft Access; advanced topics assume that you are familiar with writing Access Basic programs. This paper also assumes that you know how to create triggers on Oracle 7.1.
If you have Access installed on your personal computer and you have access to Oracle 7.1 from your personal computer you can experiment with the concepts discussed in this paper. To write stored procedures and triggers on Oracle you can use Oracle’s SQL*Plus utility.

Topics Covered
This paper contains three sections and several appendices.
"The Access / Oracle Client–Server Architecture" provides an overview of the major components involved in coupling Access to Oracle.
"Migrating Access Applications to Oracle RDBMS Server" reviews the steps required to move the data management portion of an Access application to Oracle 7.1 RDBMS.
"Extending your Application" explores ways in which the Access / Oracle application can be extended using other Oracle development tools.
The appendices include detailed information about the Access / Oracle architecture. The information in the appendices will be helpful if you are interested in the details of how Access operates with Oracle or if you want to tune your system for maximum performance.

The Access / Oracle Client–Server Architecture

Access Architecture
Microsoft Access is based on a file server DBMS technology named "Jet". As shown in Figure 1, Access’ Forms, Reports and Basic code rely on Jet to manage data stored in a the native "mdb" file format.

Figure 1 – Access Architecture.
In a single–user Access application, the mdb file, as well as Access itself, are located on the same machine. In a multi–user Access application, the mdb file is placed on a file server and shared. Each client runs a copy of 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.

Jet Recordsets
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 is 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 is able to perform a full range of operations on a snapshot, query the Snapshot, base Forms and Reports on the Snapshot, etc. Changes cannot be made to Snapshot data and changes made by others users after a Snapshot has been opened are not reflected in the Snapshot.
A Dynaset, on the other hand, is a ‘live’ view of the data. When returning a Dynaset, Jet extracts the "key values" from the data and stores these 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 a Dynaset is opened, the set of key values cannot change. This means that while the data pointed to by the "key value" may change and will be 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 which are deleted after the keyset query is run 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 which 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 Access works with a RDBMS server such as Oracle.

Jet Multi–User Updates
Jet handles updates by multiple users using two methods - optimistic and pessimistic locks .
Using pessimistic locking, Jet will place a hard lock on the data page which contains the row being edited. Other users will not be able to 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 7.1 RDBMS. An optimistic locking scheme does not place 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 Enforced Referential Integrity
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.

Jet / ODBC / Oracle Architecture
Using Oracle 7.1 RDBMS Server with Access can increase the robustness and reliability of a multi–user system. Network traffic is reduced because only the 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 7.1 RDBMS on the other hand is mature central server technology focused on multi–user performance, rollback and recovery, and centralized query processing.
Obtaining adequate performance from the combination of Access and Oracle requires an understanding of how Jet works with centralized servers.
Figure 2 shows that Access requires ODBC to make its connection to Oracle 7.1 RDBMS.

Figure 2 – Access / ODBC / Oracle Architecture.
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 Access uses ODBC to connect to remote RDBMS servers, Jet continues to function as the DBMS engine for Access. 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.
Because of the way Jet works with remote 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 will only open a non–updateable Snapshot on the table.

Migrating Access Applications to Oracle RDBMS Server
Overview

This section describes how to move the data management portion of a working Access application to Oracle. By following the steps in this section you can create an Access / Oracle client–server version of your application.

To move the data management portion of an Access application to Oracle you must first separate the data and declarative referential integrity portion of the application from the forms, reports and code in the application. After this is done, the database structure and data is moved to Oracle. Counter field equivalents are created in Oracle and the referential integrity support is added. Finally the remote tables must be attached to Access and the application can then be tested.
After you are comfortable with the migration process, please refer to the Appendices for more information including sample Oracle trigger code, information on security, and performance tuning suggestions.

Migration in Detail
Access applications can be contained in a single mdb file or multiple mdb files. Multiple mdb files are used to separate the data and application (form, report, code) portions of an application. Generally the data mdb file is shared on a central server and the application mdb file is located on each client machine.
When an application is in a single mdb file, you will need to take steps to separate the data from the rest of the application (forms, reports, code, etc.).
Remember to make backup copies of your Access application and data files before performing any of the following steps!

Preparing a Single mdb File Application for Exporting
Follow these steps to create two mdb files (one containing data, the other your application) from a single mdb file. These steps assume that you are starting with a file called app.mdb; at the end of these steps you will have app.mdb and data.mdb.
1] Make a backup of app.mdb.
2] Start Access and use the File…Compact Database menu to compress app.mdb.
3] In File Manager, copy app.mdb to data.mdb.
4] Open data.mdb in Access, delete all forms, reports, modules and macros.
5] In Access, open app.mdb and delete all the tables.
6] While still in app.mdb use File…Attach Table to make an attachment in app.mdb to each table in data.mdb.

At this point your application should run as it did before you split it into two mdb files . You are now ready to export the data.mdb file to Oracle.

Exporting Access Data to Oracle
Figure 3 illustrates an Access application before and after migration to Oracle. Both before and after migration, app.mdb contains the forms, reports, macros and Basic modules which make up the application.
Before migration, app.mdb contains an attached table from data.mdb. (We will use <tablename> to refer to the name of this table. Substitute the name of your actual tables, for example, "Customers".)
After migration, app.mdb will have two attached tables for each original table and a name mapping query. The original <tablename> from data.mdb will be attached as L_<tablename> (e.g., L_Customers). The original table will be exported to Oracle and on Oracle will be called O_<TABLENAME> (e.g. O_CUSTOMERS). An attachment will be created to O_<TABLENAME> inside Jet and will be named R_<tablename>.

Figure 3 – Access Application before and after Migration to Oracle.
Because the forms, reports and modules in app.mdb are expecting a table called <tablename> a name mapping query called <tablename> is added. This query will take the place of the original table in the application. This query can refer to either L_<tablename> or R_<tablename>; you can switch between the local and remote table as you move your application to Oracle. The name mapping query layer also helps resolve reserved word conflicts between Access and Oracle. For example, a column named "Sequence" must be renamed (e.g., O_Sequence) as this is an Oracle reserved word. The name mapping query can re-map O_Sequence back to Sequence for use by the Access application.
You will also need to move any referential integrity definitions to Oracle and install triggers to support counter fields. In the next section, a detailed set of steps are described to lead you through this process.

Migration, Step by Step
The following sequence of steps will guide you as your migrate to Oracle. They assume that you are starting with separate application and data "mdb" files named app.mdb and data.mdb.
1] Make backup copies of app.mdb and data.mdb.
2] Insure that an ODBC connection exists to your Oracle database.
3] In app.mdb, rename each attached table to be prefixed with "L_"
(e.g. rename <tablename> to L_<tablename>.)
4] Open data.mdb inside of Access and prepare each table for migration:
a) Make a copy of the table, naming the new table O_<TABLENAME>.
That is, "O_" prepended to the table name, in all capital letters .
b) Make sure that the column names follow the requirements for names in Oracle .
Change all column names to upper case.
c) Select the File…Export Menu in Access.
d) Follow the dialogs and these steps to export each table:
i Select export to <SQL Database>.
ii Select the table to export.
iii Name the Oracle table (use O_<TABLENAME>).
iv Select your ODBC DSN.
v Supply your logon information.

If you have checked for Oracle reserved words, there should be no errors to this point. If your tables are large, it could take many minutes to several hours to export each table. (Access provides a percentage complete indicator while exporting each table).
e) Delete "O_<TABLENAME> from the data.mdb file.
6] Close the data.mdb file.
7] Use File…Compact with data.mdb to recover the space used for the table copies.
8] Open app.mdb file.
9] Attach each O_<TABLENAME> table from Oracle to app.mdb using the following steps:
a) Select File…Attach Table menu.
b) Select <SQL Database>.
c) Select your ODBC DSN.
d) Supply your Oracle Logon information.
e) Select the table and press the Attach button.
10] Change the names of the attached tables from O_<TABLENAME> to R_<tablename>.
11] Create a mapping query for each attached Oracle table. The name of the query will be
<tablename>, the original name of the table, as seen by your application. Make sure that
the name of each column is mapped back to the original name found in the original tables .
12] Open the attached tables in datasheet view or open a form on the tables to make sure that the
exporting and mapping steps have been successful. You will notice that you are not able to
update the data in the tables. After you complete the migration steps and build primary keys,
you will be able to modify your data.
13] For each column which was derived from an Access Counter field (counter fields are mapped to
NUMBER(10,0)) perform the following steps :
a) Create a Sequence for each counter. You are free to choose any starting number and
increment number, however you must insure that you do not overlap numbers that have been
exported from Access. You might want to start the sequence at the next major increment.
For example, if 258 is the largest value in a counter field, you might want to start at 1000; this will make it clear which records were inserted after the move to Oracle.
b) Create trigger code for the counter field which uses the sequence .
c) Make the ‘counter field’ column the Primary key or at least create a unique index on the
column; you must insure that this index is the one selected by Access as the ‘key value’
index, so you may want to prepend "aaaa" to the name of the index.
15] Create primary key and foreign key definitions in Oracle to match the Access structure.
Oracle supports declarative Cascade Delete relationships, but not Cascade Updates. To support
Cascade Updates, you will need to write trigger code in Oracle .
16] Map default value definitions to Oracle .
17] Map row and table validation statements to Oracle CHECK statements .
18] If you have any tables in Oracle which you need to update from Access and which do not, at
this time, have a primary key, you must define one. As in step 13 above, you must make the
primary key index the first index in alphabetic order for the table.
19] Finally, use the Access Attachment Manager to refresh the attached table connections. This
insures that Jet caches the latest information about primary keys and other table parameters.
After you complete this step you will be able to update your tables.
20] Open the tables in app.mdb in a datasheet or form to insure that the migration was successful.

Extending your Application
After you move the data management portion of your Access application to Oracle you can rely on Oracle 7.1 RDBMS to protect your data, maintain all referential integrity and business rules that you have encoded in PL/SQL.
With this foundation, you can extend your application with Access or a wide range of other tools. Oracle offers several high productivity tools such as Oracle Power Objects, the Developer/2000 toolset, and Oracle Objects for OLE. Oracle Objects for OLE (OO4O) is a high performance connectivity solution for Visual Basic, Delphi and other client tools which can control OLE Automation Servers.
In addition, if your application grows, you can move your Oracle server to larger computers without changing your application.


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

Appendices

Appendix 1 - Oracle Reserved Words
The following list of Oracle reserved words are from the Oracle RDBMS Server Manual and the PL/SQL Users Manual. In forming Oracle names the valid character set includes the upper and lower case letters (A..Z, a..z) and $, _, # and numerals (0..9) after the first character. Unlike Access, spaces are not allowed.
It is suggested that all Oracle identifiers be exclusively upper case. While Oracle supports lower case identifiers, some client tools required that identifiers be surrounded by quotes if an identifier contains a lower case letter.

ABORT
ACCEPT
ACCESS
ADD
ALL
ALTER
AND
ANY
ARRAY
ARRAYLEN
AS
ASC
ASSERT
ASSIGN
AT
AUDIT
AUTHORIZATION
AVG
BASE_TABLE
BEGIN
BETWEEN
BINARY_INTEGER
BODY
BOOLEAN
BY
CASE
CHAR
CHAR_BASE
CHECK
CLOSE
CLUSTER
CLUSTERS
COLAUTH
COLUMN
COLUMNS
COMMENT
COMMIT
COMPRESS
CONNECT
CONSTANT
COUNT
CRASH
CREATE
CURRENT
CURRVAL
CURSOR
DATA_BASE
DATABASE
DATE
DBA
DEBUGOFF
DEBUGON
DECIMAL
DECLARE
DEFAULT
DEFINITION
DELAY
DELETE
DELTA
DESC
DIGITS
DISPOSE
DISTINCT
DO
DROP
DUAL
ELSE
ELSEIF
END
ENTRY
EXCEPTION
EXCEPTION_INIT
EXCLUSIVE
EXISTS
EXIT
FALSE
FETCH
FILE
FLOAT
FOR
FORM
FROM
FUNCTION
GENERIC
GOTO
GRANT
GROUP
HAVING
IDENTIFIED
IF
IMMEDIATE
IN
INCREMENT
INDEX
INDEXES
INDICATOR
INITIAL
INSERT
INTEGER
INTERSECT
INTO
IS
LEVEL
LIKE
LIMITED
LOCK
LONG
LOOP
MAX
MAXEXTENTS
MIN
MINUS
MLSLABEL
MOD
MODE
MODIFY
NATURAL
NEW
NEXTVAL
NOAUDIT
NOCOMPRESS
NOT
NOWAIT
NULL
NUMBER
NUMBER_BASE
OF
OFFLINE
ON
ONLINE
OPEN
OPTION
OR
ORDER
OTHERS
OUT
PACKAGE
PARTITION
PCTFREE
POSITIVE
PRAGMA
PRIOR
PRIVATE
PRIVILEGES
PROCEDURE
PUBLIC
RAISE
RANGE
RAW
REAL
RECORD
RELEASE
REM
RENAME
RESOURCE
RETURN
REVERSE
REVOKE
ROLLBACK
ROW
ROWID
ROWLABEL
ROWNUM
ROWS
ROWTYPE
RUN
SAVEPOINT
SCHEMA
SELECT
SEPARATE
SESSION
SET
SHARE
SIZE
SMALLINT
SPACE
SQL
SQLCODE
SQLERRM
START
STATEMENT
STDDEV
SUBTYPE
SUCCESSFUL
SUM
SYNONYM
SYSDATE
TABAUTH
TABLE
TABLES
TASK
TERMINATE
THEN
TO
TRIGGER
TRUE
TYPE
UID
UNION
UNIQUE
UPDATE
USE
USER
VALIDATE
VALUES
VARCHAR
VARCHAR2
VARIANCE
VIEW
VIEWS
WHEN
WHENEVER
WHERE
WHILE
WITH
WORK
XOR

Appendix 2 - Code and Query Samples
The following sections contains samples which support counter field emulation, cascade update referential integrity, and name mapping queries.
Counter Datatype Emulation
Access supports a counter datatype. A counter provides a monotonically increasing sequence of Long Integers for a column in a native Jet DBMS file. Oracle supports sequences. Sequences generate a set of numbers which can be used in columns as unique identifiers. An important difference between Access counters and Oracle sequences is that trigger code is required in Oracle to place a sequence number in a column when a new record is inserted into a table.
When Jet has an attachment to an Oracle table and an Oracle trigger changes or initializes the key values at the time of an insert (not updates), Jet will perform a sequence of queries to retrieve the new key value so that the inserted row can become a member of the Dynaset. If Jet has trouble re-selecting the inserted row the rows appears as #DELETED to the user.
The example below shows how to emulate a counter datatype in Oracle. The Oracle table is defined as:
CREATE TABLE OTBLCOUNTERTEST(
PK NUMBER (10,0),
NAME VARCHAR2 (50),
CONSTRAINT PK_OTBLCOUNTERTEST PRIMARY KEY (PK))
An Oracle sequence is defined as:
CREATE SEQUENCE TEST INCREMENT BY 1 START WITH 1000
The trigger code is:
Create Trigger TRG_CNT_OTBLCOUNTERTEST
Before INSERT OR UPDATE on OTBLCOUNTERTEST
FOR EACH ROW
DECLARE
iCounter SCOTT.OTBLCOUNTERTEST.PRIMARYKEY%TYPE;
cannot_change_counter EXCEPTION;

BEGIN
IF INSERTING THEN
SELECT TEST.NEXTVAL into iCounter FROM dual;
:new.PRIMARYKEY := iCounter;
END IF; -- End of Inserting Code

IF UPDATING THEN
-- Do not allow the PK to be changed.

IF NOT(:new.PRIMARYKEY = :old.PRIMARYKEY) THEN
RAISE cannot_change_counter;
END IF;

END IF; -- End of Updating Code

EXCEPTION
WHEN cannot_change_counter THEN
raise_application_error(-20000,'Cannot Change Counter Value');
END;

This trigger emulates the counter datatype by trapping both INSERT and UPDATE operations on a table. On any insert the trigger will get the next value in the sequence "TEST" for the PRIMARYKEY column. On updates, the trigger checks to see if the user is trying to update the counter; if so, an exception is raised and the error is passed back to Access.
It is not recommended to silently protect the counter on update. For example, with the following code:
IF UPDATING THEN
-- Do not allow the PK to be changed.

IF NOT(:new.PRIMARYKEY = :old.PRIMARYKEY) THEN
:new.PRIMARYKEY := :old.PRIMARYKEY);
END IF;

END IF; -- End of Updating Code

Jet becomes confused in its management of the Dynaset and produces strange results to the user.
As a possible enhancement to strict counter field emulation, one could use the following code in the trigger to allow Access to pass a value for the counter on a row insert:
IF INSERTING THEN
IF (:new.PRIMARYKEY IS NULL) THEN
SELECT test.NEXTVAL into iCounter FROM dual;
:new.PRIMARYKEY := iCounter;
END IF:
END IF; -- End of Inserting Code

This code will generate a new counter value only if the passed value is NULL.
Name Mapping Query
It is easy to build a name mapping query in Access. Use either the QBE or SQL window to define the query. In this example, the original Access table is called SeqDateTable and is exported to Oracle as O_SEQDATETABLE. After the export the table is attached to Jet as R_SeqDateTable.
When the following query is saved as SeqDateTable, it will take the place of the original table and complete the mapping to Oracle. The query maps the column names PRIMARYKEY, O_SEQUENCE and FIRSTDATE to PrimaryKey, Sequence and FirstDate for use by Access.
SELECT NameMapper.PRIMARYKEY AS PrimaryKey,
NameMapper.O_SEQUENCE AS Sequence,
NameMapper.FIRSTDATE AS FirstDate
FROM R_SEQDATETABLE;
Default Values
Oracle supports declarative default values. However, when moving an application from Access to Oracle, you may encounter situations where you need an insert trigger to support defaults. A reasonable design decision is to move all default processing to triggers to centralize the code and reduce maintenance complexity. The following code sample demonstrates supporting default values in a trigger:
CREATE OR REPLACE TRIGGER BIU_M2
BEFORE INSERT OR UPDATE
ON M2
FOR EACH ROW

BEGIN
IF INSERTING THEN
/* Manage Default Values if a new value is NULL */
IF :new.Address IS NULL THEN
:new.Address := 'Default';
END IF;
END IF; -- End of Inserting Code
END; -- Trigger BI_M2
Column and Table Validation
Oracle supports CHECK statements which can be used to enforce Table and Column constraints. However, when moving an application from Access to Oracle, you may encounter situations where you need an insert trigger to support validation. The following code sample demonstrates supporting default values in a trigger. Notice that <Access Validation Code > indicates where you can insert the validation code from an Access Application .
CREATE OR REPLACE TRIGGER BIU_M2
BEFORE INSERT OR UPDATE
ON M2
FOR EACH ROW

BEGIN
-- Validation Code
IF NOT ( <Access Validation Code > ) THEN
raise_application_error (-20000, '<Access Error Message>');
END IF;
END; -- Trigger BI_M2
Cascade Update Trigger Code
Oracle does not provide direct support for Cascade Update referential integrity constraints. Cascade Update support means that when a Primary Key is changed, that change will be made to all associated Foreign Keys in linked tables. It is not surprising that Oracle does not support this capability, as it not a common design feature in applications. Primary Keys are supposed to be stable, usually for the life of an application.
The following code example is based on two tables:
create table M1 (
f1 number,
f2 number,
f3 number )
create table M2 (f1 number,
f2 number,
f3 number )
alter table M1 add primary key (f1)
alter table M2 add primary key (f1)
This definition will support one–to–many cardinality. To add support for one–to–one cardinality add the following:
alter table M1 add constraint uq_M1_001 unique (f2, f3)
alter table M2 add constraint uq_M2_001 unique (f2, f3)
The following code implements update cascade code for the two tables, M1 and M2. Not that this example uses two columns in the Primary / Foreign Key relationships. This is more complex than most relationships and is used to fully illustrate the proper code.
Please note that declarative and procedural support for RI cannot coexist between two tables. To support Cascade Update between two tables, all declarative Primary / Foreign Key relationships and RI between the tables must be removed and supported instead with procedural code.
CREATE OR REPLACE PACKAGE P_M1 AS
fire_trigger boolean := TRUE;
END P_M1;

CREATE OR REPLACE PACKAGE P_M2 AS
fire_trigger boolean := TRUE;
END P_M2;

CREATE OR REPLACE PACKAGE UQ_M1_M2 AS

PROCEDURE cascade_update (
o_F2 IN number,
o_F3 IN number,
n_F2 IN number,
n_F3 IN number,
bResult OUT boolean );

PROCEDURE cascade_delete (
F2 IN number,
F3 IN number,
bResult OUT boolean );

FUNCTION pk_exists (
F2 IN number,
F3 IN number) RETURN boolean;

FUNCTION fk_exists (
F2 IN number,
F3 IN number) RETURN boolean;

END UQ_M1_M2;


CREATE OR REPLACE PACKAGE BODY UQ_M1_M2 AS

/* Procedure cascade_update is called when field(s) */
/* F2 or */
/* F3 */
/* are changed in table M1. */
/* The changes are cascaded in table M2 */

PROCEDURE cascade_update (
o_F2 IN number,
o_F3 IN number,
n_F2 IN number,
n_F3 IN number,
bResult OUT boolean ) IS

CURSOR d_cur (n1 number, n2 number) IS
SELECT * FROM m2
WHERE f2 = n1 AND f3 = n2
FOR UPDATE of f2, f3;

BEGIN

FOR d_cur_rec IN d_cur ( o_F2, o_F3 )
LOOP
UPDATE M2 SET f2 = n_F2, f3 = n_F3
WHERE CURRENT OF d_cur;
END LOOP; -- Detail Record Loop

bResult := true;

END cascade_update;

/* Procedure cascade_delete is called when a record */
/* in M1 is being deleted and associated */
/* child records in M2 must also be deleted. */

PROCEDURE cascade_delete (
F2 IN number,
F3 IN number,
bResult OUT boolean ) IS

CURSOR d_cur (n1 number, n2 number) IS
SELECT * FROM m2
WHERE f2 = n1 AND f3 = n2
FOR UPDATE;

BEGIN

FOR d_cur_rec IN d_cur ( F2, F3 )
LOOP
DELETE FROM M2
WHERE CURRENT OF d_cur;
END LOOP; -- Detail Record Loop

bResult := true;

END cascade_delete;

/* Procedure pk_exists is called to determine is a given
primary key exists in table M1 */

FUNCTION pk_exists (
F2 IN number,
F3 IN number) RETURN boolean IS

l_F2 number;
l_F3 number;
bResult boolean;

CURSOR p_cur (n1 number, n2 number) IS
SELECT F2, F3 FROM m1
WHERE f2 = n1 AND f3 = n2;

BEGIN
OPEN p_cur( F2, F3 );
FETCH p_cur INTO l_F2, l_F3;
IF p_cur%NOTFOUND THEN
bResult := false;
ELSE
bResult := true;
END IF;

CLOSE p_cur;

RETURN( bResult );

END pk_exists;

/* Procedure pk_exists is called to determine is a given
primary key exists in table M1 */

FUNCTION fk_exists (
F2 IN number,
F3 IN number) RETURN boolean IS

l_F2 number;
l_F3 number;
bResult boolean;

CURSOR d_cur (n1 number, n2 number) IS
SELECT F2, F3 FROM m2
WHERE f2 = n1 AND f3 = n2;

BEGIN
OPEN d_cur( F2, F3 );
FETCH d_cur INTO l_F2, l_F3;
IF d_cur%NOTFOUND THEN
bResult := false;
ELSE
bResult := true;
END IF;

CLOSE d_cur;

RETURN( bResult );

END fk_exists;

END UQ_M1_M2;


CREATE OR REPLACE TRIGGER AUD_M1
AFTER UPDATE OR DELETE
ON M1
FOR EACH ROW

DECLARE
bResult_OK BOOLEAN;
bCascadeDeletes BOOLEAN := TRUE;

BEGIN

IF UPDATING THEN
IF (:old.F2 <> :new.F2) OR (:old.F3 <> :new.F3) THEN
P_M2.fire_trigger := FALSE;
UQ_M1_M2.cascade_update( :old.F2, :old.F3, :new.F2, :new.F3,
bResult_OK );
P_M2.fire_trigger := TRUE;
END IF;
END IF; -- End of Updating Code

IF DELETING THEN
IF bCascadeDeletes THEN
UQ_M1_M2.cascade_delete( :old.F2, :old.F3, bResult_OK );
ELSE
IF UQ_M1_M2.fk_exists( :old.F2, :old.F3 ) THEN
raise_application_error( -20000, 'Rows exist in child table');
END IF;
END IF;
END IF; -- End of Deleting Code

END; -- Trigger AUD_M1


CREATE OR REPLACE TRIGGER AIU_M2
AFTER INSERT OR UPDATE
ON M2
FOR EACH ROW

DECLARE
bResult_OK BOOLEAN;

BEGIN

IF INSERTING THEN
IF NOT( UQ_M1_M2.pk_exists( :new.F2, :new.F3 ) ) THEN
raise_application_error (-20000, 'No corresponding row in parent
table');
END IF;
END IF; -- End of Inserting Code

IF ( UPDATING AND P_M2.fire_trigger ) THEN
IF NOT( UQ_M1_M2.pk_exists( :new.F2, :new.F3 ) ) THEN
raise_application_error (-20000, 'No corresponding row in parent
table');
END IF;
END IF; -- End of Updating Code

END; -- Trigger AUD_M2
Appendix 3 - Jet Architecture Details
The Jet DBMS Engine is Microsoft’s client and file server RDBMS technology. It is not sold separately, but is included in all Access versions, Visual Basic version 4.0 and Visual C++ V 4.0 . Jet is implemented in a set of DLLs. Figure 5 shows how Jet integrates with Access (and Microsoft Visual Basic).