4
Troubleshooting

This chapter provides troubleshooting solutions. It instructs you through a manual conversion of a Microsoft Access database to Oracle. This guide assumes that you are starting with a separate application and data .MDB files, the app.mdb file and the data.mdb file. It instructs you on how to separate these files prior to performing a manual conversion.

This chapter includes the following sections:

Removing Microsoft Access Security

The Migration Workbench does not support the migration of Microsoft Access databases that have security enabled. 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 migrate the new database to Oracle.

To copy the contents of the secured database into a new database:

In Microsoft Access, choose File -> New Database.
Select the Blank Database icon and click OK.
From the File New Database dialog box, type a name for the database and click Create.
From within the new database, choose File -> Get External Data -> Import.
Select the secured Microsoft Access database you want to import and click Import.
From the Import Objects dialog, click Options. Ensure that the Relationships and Definition and Data options are selected.
From the Tables tab, choose Select All.
Click OK.

Erroneous Relations within Microsoft Access 95

There is a bug within Microsoft Access 95 that sometimes generates false relations. Since these erroneous relations do not appear in the Microsoft Access IDE user interface, you cannot delete them through the application because they are hidden from the user. The workaround is to create a new copy of the Microsoft Access database with no relations specified. You can then manually re-create the correct relations in the new database.

To copy the contents of the Microsoft Access database without specifying relations:

Create a new empty Microsoft Access database by choosing File -> New Database.
Select Blank Database and click OK.
From the File New Database dialog box, type a name for the database and click Create.
From within the new database, choose File -> Get External Data -> Import.
Select the original Microsoft Access database you want to import.
From the Import Objects dialog, select Options.
Deselect Relationships from the Import section, then click OK.

Note:
If you want to migrate the legitimate relations from Microsoft Access, you must manually re-create them within the new database.
From the Tables tab, choose Select All.
Click OK.

Migrating Relations with Queries

The Migration Workbench currently does not support the migration of queries. Therefore, any relations consisting of queries do not migrate to Oracle. To avoid errors in migrating the Microsoft Access to Oracle, manually delete the relations by doing the following:

From within the Microsoft Access database, choose Tools -> Relationships.
Right-click on the line that shows the relationship, then select Delete Relationship.
Select Yes to confirm deletion.
Migrate the Microsoft Access database to Oracle.

Defining Primary Keys on a Table

If a table you specify does not have a defined primary key, you are unable to update the records of that table after migration. Microsoft Jet database engine requires a primary key on tables in Oracle in order to support dynasets against those tables. If the Oracle table does not have a primary key, Microsoft Jet database engine opens only a non-editable snapshot on the table. If you want to update the contents of the table through the Microsoft Jet database engine, you should do one of the following:

Ensure that the original Microsoft Access table has a primary key defined on it. You must re-capture the Microsoft Access database to ensure the primary key is applied to the Oracle table.
Define a primary key on the migrated Oracle table before you use the Migration Workbench to modify the Microsoft Access database. For example, you would use the following syntax to define a primary key:
```
alter table <user>.<table> add ( constraint <primary key name> primary key ( 
<column name> ));
```
Therefore, if you substituted the variables user for Scott, table for Categories, primary key name for PK_CATEGORIES, and column name for ID, you would apply the following syntax:
```
ALTER TABLE Scott.Categories ADD ( CONSTRAINT PK_CATEGORIES PRIMARY KEY
( ID ) );
```

Migrating Table Data Using Microsoft Access ODBC Data Source

The following message displays during the data move of the migration process if the ODBC data source is not referencing a valid .mdb file:

The Microsoft Access ODBC Data Source Name "omwb_msaccess" is not configured 
properly

When this message displays, perform the following procedures:

Choose Start->Settings->Control Panel.
Select ODBC Data Sources.
Select omwb_msaccess from the User DSN tab.
Click Configure.
Click Select in the Database section.
Browse to the database you want to migrate and click OK.

Incorrect Boolean Values When Migrating Data

When the Migration Workbench retrieves Boolean data, the Microsoft Access ODBC driver returns one of two values, 0 as FALSE or 1 as TRUE, therefore, FALSE and TRUE values are also represented in Oracle as 0 and 1.

However, an issue may arise when this data is used within a Microsoft VB application. Microsoft VB internally represents TRUE as -1 (instead of 1), and FALSE as 0. Therefore if you are evaluating based on TRUE, the logical programme behavior may be incorrect.

To ensure that the logic is unchanged, Oracle recommends that you update TRUE Boolean values in Oracle tables by executing the following command:

UPDATE <TABLENAME> SET <COLUMNNAME> = -1 WHERE <COLUMNAME> = 1

4 Troubleshooting