|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 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:
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:
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:
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:
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:
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:
The following message displays during the data move of the migration process if the ODBC data source is not referencing a valid .mdb file:
When this message displays, perform the following procedures:
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: