3
Microsoft Access Questions

This chapter contains frequently asked questions about using the Oracle Migration Workbench to migrate from a Microsoft Access 2.0, 95, 97, or 2000 database to an Oracle Server. It contains the following sections:

• Pre-Installation
  
  
• Installation and Configuration
  
  
• Data Migration

Pre-Installation

This section contains Microsoft Access pre-installation questions.

Does the Migration Workbench support Microsoft Access 2000 migration to Oracle?

Yes. The Migration Workbench release 9.2.0 supports the migration of Microsoft Access 2000 to a destination Oracle database. In order to migrate a Microsoft Access 2000 database, you must have Microsoft Access 2000 installed on your system.

How do I migrate a Microsoft Access database to an Oracle database?

Before you can migrate a Microsoft Access database to an Oracle database, you must first export the MDB file to an XML file. Use the Oracle Migration Workbench Exporter for Microsoft Access to do this. This is located in the %ORACLE_HOME%\omwb\msaccess_exporter directory.

The Oracle Migration Workbench Exporter extracts the schematic information from the MDB file and stores it in the XML file. The Migration Workbench then uses this XML file to capture the Microsoft Access database within the Capture Wizard.

If the source Microsoft Access database uses linked tables, the schematic information for the attached MDB files are also automatically stored in the XML file.

You can export and capture only one Microsoft Access database at a time.

Installation and Configuration

This section contains Microsoft Access installation and configuration questions.

What software do I need to migrate a Microsoft Access 97 database to an Oracle database?

In order to migrate a Microsoft Access 97 database to an Oracle database, you must have the following software installed on the same system as the Migration Workbench:

• An ODBC driver release 4.00.4202.00 or greater.

  You can obtain this by downloading the latest version of the Microsoft Data Access Components (MDAC) from the Microsoft Web site at:

  http://www.microsoft.com

• Microsoft Data Access Objects (DAO) release 3.5.

  You can obtain this by downloading Microsoft Access 97.

• Microsoft Access 97.
  
  
• An Oracle ODBC driver.

  This allow Microsoft Access forms and reports to work with the migrated data. To install the Oracle ODBC driver, select the Custom Install option when installing the Migration Workbench. After you have installed the Oracle ODBC driver, you should set the Oracle ODBC Data Source.

  See Also: For more information, see Setting the Oracle ODBC Data Source in the Oracle Migration Workbench Online Help.

What software do I need to migrate a Microsoft Access 2000 database to an Oracle database?

To migrate Microsoft Access 2000 database to an Oracle database, you must have the following software installed on the same system as the Migration Workbench:

• An ODBC driver release 4.00.4202.00 or greater. You can obtain this by downloading the latest version of the Microsoft Data Access Components (MDAC) from the Microsoft Web site at:

  http://www.microsoft.com

• Microsoft Data Access Objects (DAO) release 3.6. You can obtain this by downloading Microsoft Access 2000.
  
  
• Microsoft Access 2000.
  
  
• You must install the Oracle ODBC driver to enable Microsoft Access forms and reports to work with the migrated data. To install the Oracle ODBC driver, select the Custom Install option when installing the Migration Workbench. After you have installed the Oracle ODBC driver, you should set the Oracle ODBC Data Source.

  See Also: For more information, see Setting the Oracle ODBC Data Source in the Oracle Migration Workbench Online Help.

  Note: You must install the Oracle ODBC driver version 9.0.1

What happens if the following message appears when you log into the Oracle Migration Workbench: "There is no Microsoft Access ODBC driver software installed on this system. Please install the software before using the Microsoft Access plugin."

If the Microsoft Access ODBC driver is installed correctly, this issue may occur because you are logged on to a system where another user (with different user privileges) has installed the Microsoft Access Database.

To resolve this issue you must have the same privileges as the other user.

Data Migration

This section contains Microsoft Access data migration questions.

Why does the Oracle Model hang when I map foreign keys?

If you have defined a foreign key relationship between two tables outside of the MDB file that contains these tables, such as between two linked tables, the Oracle Model might hang. To avoid this problem, define the table relationship between tables within the MDB file.

What should I do if I receive the "ORA-00001: Unique constraint violated message displayed in error.log or as an alert" error message?

If you receive the ORA-00001 error message, the Migration Workbench hangs while it loads indexes.

You might receive this error if there is a problem with the owner profile within the Migration Workbench Repository or if the administrator of the Microsoft Access database does not have full write permissions. You may also receive this error message if the PL/SQL package has not been created properly.

Granting Permissions

The ORA-00001error message can appear if the Microsoft Access database administrator does not have full permissions. To fix this problem:

1. Check the security of the administrator in Microsoft Access by choosing Tools > Security > User and Group Permissions.
   
   
2. Make sure that all of the permission options are selected for the administrator.

Creating PL/SQL Packages

If the PL/SQL package was not created correctly in the Oracle database, no triggers associated with the Microsoft Access Source Model tables work. The Source Model uses sequence and trigger pairs to generate unique IDs for some of the columns within the tables.

To check if the PL/SQL package was created properly, log in to SQL*Plus and execute the following SELECT statement:

select tblid from acc_table;

If all values returned by this statement are -1, the trigger that the Migration Workbench uses to return unique table IDs was not created.

You can test if the Migration Workbench correctly created the triggers by executing the following statement:

select trigger_name from all_triggers where table_name in ('ACC_DB_INFO', 'ACC_
TABLE');

If the following names are not returned by this statement then the triggers were never created. If this is the case, consult with the DBA and investigate why triggers were not created.

ACC_TR_DB_INCR 
ACC_TR_TBL_INCR 

If the previous names are returned, you should verify that the triggers work. The ACC_DB_INFO table has a sequence/trigger pair defined on the DBID column. To verify that the triggers work:

1. Execute the following statement several times:
   
   

   insert into ACC_DB_INFO values (999,'f',12,'d','a','s',1,1,1);
   
   

2. Perform a select of the DBID value in the ACC_DB_INFO table to ensure that the values in the DBID column are sequential.

If the trigger does not work, the value specified in the INSERT statement (in our example 999) for the DBID column is displayed.

To test whether you can manually create the sequence/trigger pair execute the following commands:

DROP SEQUENCE ACC_SEQ_DB_INCR; 
CREATE SEQUENCE ACC_SEQ_DB_INCR START WITH 1; 
CREATE OR REPLACE TRIGGER ACC_TR_DB_INCR 
BEFORE INSERT ON ACC_DB_INFO 
FOR EACH ROW 
BEGIN 
SELECT ACC_SEQ_DB_INCR.NEXTVAL INTO :NEW.DBID FROM DUAL; 
END; 
/

Consult the DBA if you receive any error messages when attempting to execute the previous commands.

After you have attempted to manually create the sequence and trigger, the following error might display:

ORA-06554 package DBMS_STANDARD must be created before using PL/SQL 

This error indicates that there is something wrong with the way in which you set up the database. To correct this, create a new database instance using the database configuration assistant that ships as standard with Oracle9i or Oracle8i.

What if I receive one of the following messages while attempting to launch the Oracle Migration Workbench Exporter for Microsoft Access:

• Prompted to associate a file type with the omwb.mde file
  
  
• Incorrect version of Microsoft Access is executed
  
  
• Error: ActiveX component can't create object
  
  
• Error: Unable to convert or enable MDE file.

The version of Microsoft Access must be compatible with the Microsoft Access database that you want to migrate. If you receive one of the preceding error messages, you might have a version of Microsoft Access that is incompatible with the Microsoft Access database that you are migrating. If you have more than one version of Microsoft Access installed on the same system, the incorrect version of the Oracle Migration Workbench Exporter for Microsoft Access might execute.

To ensure that you use the correct version of the Oracle Migration Workbench Exporter for Microsoft Access (omwb.mde) on the system:

1. Start the version of Microsoft Access associated with the database that you would like to migrate to a destination Oracle database. For example, if you are migrating a Microsoft Access 2000 database, open Microsoft Access 2000. If you are migrating any other Microsoft Access database, open Microsoft Access 97.
   
   
2. Choose File > Open Database.
   
   
3. From the Files of Type option, select Microsoft Access Databases (*.mde).
   
   
4. Click Advanced.
   
   
5. From the Look In option, search for the omwb97.mde or omwb2000.mde files located in the %ORACLE_HOME%\Omwb\msaccess_exporter directory.

Why does the Migration Workbench not handle validation rules with the following syntax:

• between x and y
  
  
• is null

For validation rules, the Migration Workbench generates triggers. The Migration Workbench does not parse the between x and y or is null syntax correctly. In order to overcome this problem, the between x and y or is null syntax, replace the trigger text in the Oracle Model. The following table shows the incorrect syntax and the correct syntax that you should use to edit the code of the trigger.

IF NOT 
(:new.column>new.column=x 
AND:new.column=y 
AND:new.column<=:new.column is 
Null) THEN 

IF NOT (:new.column>=x 
AND:new.column<=y OR:new.column is 
Null) THEN 

Can I migrate my Microsoft Access security settings to an Oracle Server?

No. The Migration Workbench does not support migrating Microsoft Access databases that have security enabled. To ensure that the Migration Workbench can migrate the Microsoft Access table data, copy the contents of the secured database into a new database. Everything is copied to the new database, except the security settings. You can then migrate the new database to an Oracle Server.

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

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

How do I avoid erroneous relations within Microsoft Access 95?

There is a bug within Microsoft Access 95 that sometimes generates erroneous relations. Since these erroneous relations do not appear in the Microsoft Access IDE user interface, you cannot delete them by using the application because they are hidden from the user. To correct this problem, 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:

1. Create a new empty Microsoft Access database by choosing File > New Database.
   
   
2. Select Blank Database and click OK.
   
   
3. From the File New Database dialog box, type a name for the database and click Create.
   
   
4. From within the new database, choose File > Get External Data > Import.
   
   
5. Select the original Microsoft Access database that you want to import.
   
   
6. From the Import Objects dialog, select Options.
   
   
7. 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.

8. From the Tables tab, choose Select All.
   
   
9. Click OK.

   Note: If you want to migrate the legitimate relations from Microsoft Access, you must manually re-create them within the new database.

Can I migrate a replica Microsoft Access database?

No. The Migration Workbench does not permit migration of replica Microsoft Access databases.

Can I use the Migration Workbench to migrate multiple Microsoft Access databases to a single Oracle user?

Yes. To migrate multiple Microsoft Access databases to a single Oracle user, give all of the Microsoft Access databases the same name. All of the Microsoft Access databases must have the same name because the Migration Workbench uses the name of the Microsoft Access MDB file as the user name for the destination Oracle user.

Ensure that all the Microsoft Access databases with the same name reside in different directories from each other. Then, use the Capture Wizard to select the databases for a simultaneous migration.

Microsoft Access databases that have been captured in the Source Model appear individually, however, they all appear with the same name. You can identify a database by viewing its path in the Source Model property sheet. When you view the Oracle Model, you see that there is only one user. This user contains all schema objects from the multiple Source Model databases.

You can have a Microsoft Access application database that contains linked tables to another Microsoft Access database. To migrate multiple Microsoft Access databases containing attached tables to a single Oracle user:

1. Rename the databases to the name of the Oracle user where you want to migrate, for example employee.mdb.
   
   
2. Open the first employee.mdb file in Microsoft Access, then choose Tools > Add-Ins >Linked Table Manager to refresh the links.
   
   
3. From the Migration Workbench, choose the Action>Capture Source Database option to start the Capture Wizard.
   
   
4. Within one of the steps of the Capture Wizard you are asked to add an Microsoft Access database. Select the first employee.mdb file, then proceed with migration.
   
   
5. Use the Capture Wizard to migrate the next employee.mdb file.

   Note: If schema objects of the same type have the same name in multiple Microsoft Access databases the Migration Workbench automatically detects and resolves this naming conflict.

   See Also: For more information on Microsoft Access architecture, see the Microsoft Access Reference Guide.

Can my hyperlinks work after I migrate my database to an Oracle Server?

No. Oracle Servers do not support hyperlinks. Therefore, the Migration Workbench simply brings over the raw contents of the column.

What does the message "No primary key defined on table name; you are unable to update records after migration." mean?

This error message occurs because the specified table does not have a primary key defined on it. Jet requires a primary key on tables in Oracle databases in order to support dynasets against those tables. If the Oracle database table does not have a primary key, Jet only opens a non-editable snapshot on the table. It is possible to define a table in Microsoft Access that you can update yet does not have a primary key. When you migrate the table to an Oracle database, it does not have a primary key; therefore, the Jet engine is unable to update it. If you want to update the contents of the table through the Jet engine, do one of the following:

• Ensure that the original Microsoft Access table has a primary key defined on it. You then must re-capture the Microsoft Access database.
  
  
• Define a primary key on the migrated Oracle database table before you use the Migration Workbench to modify the Microsoft Access database.

  See Also: For more information on Microsoft Jet error messages, see the Microsoft Access Reference Guide.

What should I do if I encounter the error message "JET/DAO Error 3050: Couldn't lock file"?

When DAO opens a Microsoft Access database, it automatically generates a LDB file. This error indicates that the LDB file is read-only and cannot be updated. To overcome this error, you should make the LDB file writable by altering the properties of the file and then reselect the Microsoft Access database for migration.

Why must I define an Oracle ODBC data source when migrating from Microsoft Access?

You must define an Oracle ODBC data source to modify the Microsoft Access database. Defining an Oracle ODBC data source allows you to continue using the Microsoft Access forms and reports after you have used the Migration Workbench to migrate the data to a destination Oracle database.

Why must I modify the Microsoft Access database?

To continue using the Microsoft Access front end with the data that you have migrated to an Oracle Server, the Migration Workbench must make some modifications to the Microsoft Access database.

To modify the Microsoft Access database, you must rename local tables. However, during the data move phase of the migration, the Migration Workbench references the original table names and expects these names to exist in the Microsoft Access database. Therefore, you should modify the Microsoft Access database after you have successfully migrated the data to Oracle.

Does the Migration Workbench support offline data loading in Microsoft Access?

Yes. The Migration Workbench uses SQL*Loader to provide an offline data loading capability, which improves the migration of large tables from Microsoft Access. To use offline data loading, you must manually create the data files from within the Microsoft Access Integrated Development Environment (IDE).

The base directory for the SQL*Loader script output for Microsoft Access is %ORACLE_HOME%\Omwb\sqlloader_scripts\MSAccess\timestamp. The default Log directory is %ORACLE_HOME%\Omwb\sqlloader_scripts\. The Migration Workbench creates a directory in this base directory that represents the date and time that you generated the SQL*Loader scripts. For example, a sub-directory called 1-10-1999_17-58-16 indicates that the scripts were generated at 17:58 p.m. on 1st October, 1999.

To manually create the data files from within the Microsoft Access Integrated Development Environment (IDE):

1. Open the Microsoft Access database in the Microsoft Access environment.
   
   
2. Select the table whose data you want to export to a data file.
   
   
3. Choose File >Save As/Export.
   
   
4. Choose the To an External File or Database option, then click OK.
   
   
5. Choose the Text Files from the Save as type box. Make sure that the Save Formatted option is not selected, then click Export.
   
   
6. Select the Delimited - Characters comma or tab separate each field option, then click Next.
   
   
7. Click Other as the delimiter that separates the fields, then insert the § character.

   Note: The § character is used by the SQL*Loader control file that is generated by the Migration Workbench. You can enter § by pressing ALT+0167.

8. Select {none} from the Text Qualifier box, then click Next.
   
   
9. Enter the following path to export the file.
   
   

   %ORACLE_HOME%\Omwb\sqlloader_scripts\MicrosoftAccess_timestamp directory.
   
   

   Note: Make sure that the name of the file is identical to the name of the table and that the extension you specify for the data file is DAT. This is the naming convention used by the SQL*Loader control file generated by the Migration Workbench.

10. Click Finish to generate the data file, then click OK.
    
    
11. Now that you have manually created the data files, you can run the sql_load_script.bat file in the %ORACLE_HOME%\Omwb\sqlloader_scripts\MicrosoftAccesstimestamp directory.

How do I migrate the Microsoft Access Memo columns in tables?

The Oracle ODBC driver automatically handles both BLOB and CLOB columns. By default, all Microsoft Access Memo fields are mapped to CLOB datatypes in an Oracle database. You can use the Microsoft Access front end with the destination Oracle database. Unlike LONG columns, from Oracle 8.x onwards you can have any number of LOB columns in a table. You can have either internal LOBs, meaning they are inside the database, or external LOBs, meaning they are in a file on the file system but under the database's transactional control. The advantages of LOBs are that you can replicate them or index them using the context option.

What should I do if the following error is reported during data load from Microsoft Access to Oracle: "EXCEPTION: LoadTableData.run() : [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides"?

This error is associated with the internal ODBC DSN which is installed with the Migration Workbench. The most likely cause is that the Microsoft Access plug-in has been reinstalled into a different Oracle Home or that the small.mdb file installed with the Migration Workbench has been deleted or moved.

To resolve this issue:

1. Choose Start > Settings > Control Panel > ODBC Data Sources > System DSN
   
   
2. Double-click Microsoft Access DSN.
   
   
3. Ensure that the Database path name is spelled correctly, and that this path points to a valid .mdb file.
   
   
4. If the path is not correct choose Select and browse to a valid mdb file location.