In this tutorial, we will illustrate the bidirectional synchronization capabilities of Scriptoria by first importing a DB2 database into Access where we will make some design changes that will in turn be reflected in the original DB2 database. We will then use Scriptoria to create a new Access application that will allow us to enter data directly into the DB2 database using Access Forms (and ODBC.)
For this example, we will import the SAMPLE database that was installed with DB2. We will use a copy of the SAMPLE database located on our Internet server, but the SAMPLE database could be located on any supported DB2 platform. (See Cataloging a Remote (Internet) DB2 Database.)
Choose Scriptoria for DB2 from the Access Tools | Add-ins | menu. The DB2 Database Selection form is displayed.
Enter a User ID and Password and then select the SAMPLE database (in our case the DB2 alias is RMSAMPLE) from the DB Name combo-box.
Load the Import DB2 Database form by choosing the Import DB2 Database item from the Scriptoria Tools menu.
Since there is no unique key defined for EMP_ACT, this table is not updatable using ODBC. We can remedy this situation by adding a new AutoNumber field to the Access table and designating it as the primary key. Note that primary keys are always unique.
By examining
the fields in each table, we can see some that obvious relationships exist.
For example, we can safely assume the EMP_ACT table is related to the
EMPLOYEE table since the EMP_ACT table contains an EMPNO field that corresponds
in name and data type with EMPNO field in the EMPLOYEE table.
By defining relationships between these tables, we can ensure that data is consistent. For example, if an attempt to insert a row in the EMP_ACT table that contains an EMPNO that does not exist in the EMPLOYEE table, an error is issued and the insert fails. In addition, if the EMPNO in the EMPLOYEE table changes, then all references to that EMPNO in the EMP_ACT table must be changed to the new EMPNO.
We can establish relationships for the EMP_ACT table by choosing the Access Tools | Relationships menu item.
Clicking on the EMPNO field in the EMPLOYEE table and dragging to the EMPNO field in the EMP_ACT table establishes a one-to-many relationship with EMPLOYEE as the parent or primary table and EMP_ACT as the child or foreign table.
Double-click on the line connecting the EMPLOYEE and EMP_ACT tables to display (and set) properties for this relationship. Checking the Enforce Referential Integrity box will provide error checking to prevent erroneous data entry. To ensure that changes to any EMPNO in the EMPLOYEES table are automatically reflected in the EMP_ACT table, the Cascade Update Related Fields box is checked.
To optimize performance, we should also create non-unique indexes for any foreign key fields in EMP_ACT. The EMPNO and PROJNO are both foreign keys as shown in the completed Relationship Diagram.
As with the EMP_ACT table, establish a foreign key relationship with the DEPARTMENT table as the parent. Specify Enforce Relationship and Cascade Update.
Specify a non-unique index on the DEPTNO foreign key.
Establish foreign key relationships between the EMP_PHOTO and EMP_RESUME tables and the EMPLOYEE table. These are one-to-one relationships. This time we will specify Cascade Delete as well as Cascade Update since it makes no sense to keep an employee’s photo and resume on file when the employee’s vital information has been deleted.
Since the primary keys of the EMP_PHOTO and EMP_RESUME tables include the EMPNO field as the first index column, we need not specify another foreign key index.
To prevent duplicates, we will build a unique index on DEPTNAME.
Click on the Scriptoria Main Menu. Notice that the tables that were modified appear in the Selected List. Press the Update DB2 button. The DB2 SAMPLE database is synchronized to reflect the changes made to the Access data model. Note that we could have chosen to update a database other than RMSAMPLE if we wished to preserve the original database or wanted to copy the database to another database or platform.