4
Troubleshooting

This chapter guides you through a manual conversion of your MS Access database to Oracle should this be necessary. It assumes that you are starting with separate application and data "mdb" files named app.mdb and data.mdb and instructs you how to separate these files prior to performing the manual conversion.

Separation of Data and Application Files

The following steps outline how to separate your data and application files. They assume that you will start with a file called app.mdb and end with two files called app.mdb and data.mdb.

1. Make a backup of app.mdb.

2. Start MS Access and use the File -> Compact Database menu to compress app.mdb.

3. In File Manager, copy app.mdb to data.mdb.

4. Open data.mdb in MS Access, delete all forms, reports, modules, and macros.

5. In MS Access, open app.mdb and delete all the tables.

6. While still in app.mdb, use the File -> Attach Table menu item to make an attachment in app.mdb to each table in data.mdb.

Now your application should run as it did before you split it into two mdb files.

Step by Step Manual Migration

The following steps guide you through the manual conversion process:

1. Make backup copies of app.mdb and data.mdb.

2. Ensure that an ODBC connection exists to your Oracle database.

3. In app.mdb, rename each attached table to be suffixed with "_L". For example, rename <tablename> to <tablename>_L.

4. Open data.mdb inside MS Access and prepare each table for migration as outlined at the end of this section.

5. Close the data.mdb file.

6. Use File...Compact with data.mdb to recover the space used for the table copies.

7. Open the app.mdb file.

8. Attach each <TABLENAME> table from Oracle to app.mdb as follows:
   • Select the File -> Attach Table menu item.

   • Select <SQL Database>.

   • Select your ODBC DSN.

   • Supply your Oracle logon information.

   • Select the table and press the Attach button.

9. Change the names of the attached tables from <TABLENAME> to <tablename>_R.

10. Create a mapping query for each attached Oracle table. The name of the query will be <tablename>, the original name of the table, as seen by your application. Make sure that the name of each column is mapped back to the original name found in the original tables.

11. Open the attached tables in datasheet view or open a form on the tables to make sure that the exporting and mapping steps have been successful. You will notice that you are unable to update the data in the tables. After you complete the migration steps and build primary keys, you will be able to modify your data.

12. Perform the following steps for each column that was derived from an MS Access COUNTER field (COUNTER fields are mapped to NUMBER(10, 0)):
    • Create a sequence for each COUNTER. You can choose any starting number and increment number; however, you must ensure that you do not overlap numbers that have been exported from MS Access. You might want to start the sequence at the next major increment. For example, if 258 is the largest value in a COUNTER field, you might want to start at 1000; this will make it clear which records were inserted after the move to Oracle.

    • Create trigger code for the COUNTER field that uses the sequence.

    • Make the COUNTER field column the primary key or at least create a unique index on the column; you must insure that this index is the one selected by MS Access as the `key value' index, so you may want to prepend "aaaa" to the name of the index.

13. Create primary key and foreign key definitions in Oracle to match the MS Access structure. Oracle supports declarative CASCADE DELETE relationships, but not CASCADE UPDATEs. To support CASCADE UPDATEs, you need to write trigger code in Oracle.

14. Map default value definitions to Oracle.

15. Map row and table validation statements to Oracle CHECK statements.

16. If you have any tables in Oracle that you need to update from MS Access and which do not have a primary key, you must define one. As in step 12 above, you must make the primary key index the first index in alphabetic order for the table.

17. Finally, use the MS Access Attachment Manager to refresh the attached table connections. This step insures that Jet caches the latest information about primary keys and other table parameters. After you complete this step you will be able to update your tables.

18. Open the tables in app.mdb in a datasheet or form to ensure that the migration was successful.

Preparing Tables for Migration

The steps outlined below explain how to prepare your tables for migration.

1. Make a copy of the table, naming the new table <TABLENAME>. That is, rename the table name in capital letters.

2. Make sure that the column names follow the requirements for names in Oracle. Change all column names to upper case.

3. Select the File -> Export menu item in MS Access.

4. The instructions that appear in the dialog boxes together with the following six steps outline how to export each table:
   • Select export to <SQL Database>.

   • Select the table to export.

   • Name the Oracle table (use <TABLENAME>).

   • Select your ODBC DSN.

   • Supply your logon information. If you have checked for Oracle reserved words, there should be no errors. However, if your tables are large, it could take up to several hours to export each table. MS Access provides a percentage complete indicator while exporting each table.

   • Delete <TABLENAME> from the data.mdb file.