4 Troubleshooting

This chapter provides troubleshooting solutions that you may need during the conversion of a Microsoft Access database to Oracle.

This chapter includes the following sections:

Section 4.1, "Removing Microsoft Access Security"
Section 4.2, "Migrating Relations with Queries"
Section 4.3, "Defining Primary Keys on a Table"
Section 4.4, "Migrating Table Data Using Microsoft Access ODBC Data Source"
Section 4.5, "Incorrect Boolean Values When Migrating Data"
Section 4.6, "Errors When Modifying a Microsoft Access Database"

4.1 Removing Microsoft Access Security

SQL Developer does not support the migration of Microsoft Access databases that have security enabled. In order to ensure that SQL Developer 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.

4.2 Migrating Relations with Queries

SQL Developer currently does not support the migration of relations consisting of queries. To avoid errors in migrating the Microsoft Access database 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.

4.3 Defining Primary Keys on a Table

If a table you specify does not have a defined primary key or unique index, you are unable to update the records of that table after migration. Microsoft Jet database engine requires a primary key or unique index on tables in Oracle in order to support dynasets against those tables. If the Oracle table does not have a primary key or unique index, 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 or unique index defined on it. You must re-capture the Microsoft Access database to ensure the primary key or unique index is applied to the Oracle table.
Define a primary key or unique index on the migrated Oracle table before you use SQL Developer to modify the Microsoft Access database. For example, 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, the statement would be as follows:
```
ALTER TABLE Scott.Categories ADD ( CONSTRAINT PK_CATEGORIES PRIMARY KEY
( ID ) );
```

4.4 Migrating Table Data Using Microsoft Access ODBC Data Source

The following message is displayed during the data move of the migration process if the ODBC data source is not referencing a valid Access database 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.

4.5 Incorrect Boolean Values When Migrating Data

When SQL Developer 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.6 Errors When Modifying a Microsoft Access Database

You may receive the following error messages while trying to modify the Microsoft Access database:

Error 3051

The Microsoft Access database cannot open the file name. It is already opened exclusively by another user or you need permission to view its data.

Solution

Ensure that you have write permissions on the Microsoft Access database.

Error 3111

Couldn't create; no modify design permission for table or query name.

Solution

Set the appropriate permissions in the Microsoft Access database. If you are not the owner of the database, this may not be feasible. In this case, a possible work around is to make a copy of the database.

To make a copy of the database:

Create a new database in Microsoft Access.
From the File menu, select Get External Data, then select Import.
Select the original database.
Select all the schema objects, then select OK.
Copy the schema objects to the new database. You now have appropriate permission in this newly created Microsoft Access database.

Note:

If the Microsoft Access database opens a table directly (not using a dynaset or snapshot), it does not work with linked tables. This restriction also applies after you move the data to Oracle. The solution is to change the application to use dynasets or snapshots.