Migrating Your Access Database to Oracle
Author: Puneet Sangal, Senior Java Developer, National Leisure Group (Woburn, Mass.)
Publication Date: December 2004

Migration to an Oracle database from an existing system can be daunting, especially when the existing database exhibits few or no similarities to the Oracle database structure. In this Technical Note I will compare and contrast two techniques for Microsoft Access-to-Oracle migration: the "raw" approach using utilities within Microsoft Access and some DOS-specific batch files, and the "refined" approach using the Oracle Migration Workbench (OMW) tool.

Manual Method

For manual migration from Access to an Oracle database, you would follow these steps:

• Define the tables in Oracle that have matching datatypes. Avoid use of CLOB in Oracle because it slows down the system; use varchar2 instead. Also, spaces in colnames or percent signs are prohibited. The words mode and comment are keywords in Oracle, so if your Access database has column names including these words, change them to other meaningful names in Oracle. Be careful with datatype conversions between date and time, numbers with and without decimals, and char vs. varchar2. There is no Boolean type in Oracle—use char(1) instead.
• Verify that the Access tables have primary keys defined (Oracle needs them for all tables). Also, Oracle is case-sensitive and Access is not. Use uppercase for all column and table names. The names should also have fewer than 30 characters in Oracle (64 in Access).
• Go to Control Panel->Administrative Tools->Data Sources (ODBC).
• On the System DSN tab, click on Add.
• Select Oracle ODBC driver, and click on Finish.
• In the ODBC Driver Setup box, type the name of the database, not the host, for the DSN.
• Type a description.
• For Data Source Service Name, again type the name of database and specify the UserID.
• Set any other options as desired, and modify the prefetch count accordingly. Note that the Oracle database from which the application is fetching rows must be 8.0.3 or later.
• Click on OK twice. Note that depending on your OS version, you may have the option of using a TNS name service directly in the driver configuration.
• Test the connection before proceeding.
• Go to Access.
• Select the tables.
• Right-click on Link Tables.
• In the dialog box, go to File Types and select ODBC() Databases.
• Go to the Machine Data Source tab, and find the DSN. Select that, and click on OK.
• Type the service name, user name, and password, if they are not already populated. For the service name, use the DSN.
• Type the service name, user name, and password, if they are not already populated. For the service name, use the DSN.
• Click on OK, and a list of tables and schemas appears. Select the tables. Click on OK.
• For tables without a primary key, you are asked to select unique record identifiers. For these tables, select No field displayed, and click on OK. You will now see all the linked tables in Access with a globe next to them.
• Go to the Queries tab.
• Select New->Find Unmatched Query Wizard.
• Select the table in Access. Click on Next.
• Select the linked table from Oracle, which is now in Access.
• If there is any primary key, match it.
• If there is none, just click on Next.
• If there is more than one, match just one of them.
• Click on Next, select all the fields, select Next->Click Modify Design, and click on Finish.
• Go to Query->Append Query.
• Select the table name as the linked table in question, select the current database if it's not already selected, and click on OK.
• In the Append To section, select the corresponding columns if they are not already selected.
• Go to the matching primary key column , and remove anything that appears in the Append To section.
• Save the query.
• If there is one primary key, you are done with this step.
• If you have more than one, go to the SQL view and modify the query to contain both primary keys—for example, if the table has two primary keys, such as Catalog# and Step#, and you used Catalog# in the Match Query wizard, then modify:

  INSERT INTO PMSANGAL_TBLSPECSTEPS ( MOD, [CATALOG#], [STEP#], LAYER, NOM_OHMS,
    NOM_OHMS, MAX_OHMS, LEFT_PERCENT, RIGHT_PERCENT )
  SELECT SpecSteps.Mode, SpecSteps.[Catalog #], SpecSteps.[Step #], SpecSteps.Layer,
         SpecSteps.[Min Ohms], SpecSteps.[Nom Ohms], SpecSteps.[Max Ohms], SpecSteps.[Left %],
         SpecSteps.[Right %]
  FROM SpecSteps LEFT JOIN PMSANGAL_TBLSPECSTEPS ON SpecSteps.[Catalog #] =
    PMSANGAL_TBLSPECSTEPS.[CATALOG#]
  WHERE (((PMSANGAL_TBLSPECSTEPS.[CATALOG#]) Is Null));
  
  

  to:

  INSERT INTO PMSANGAL_TBLSPECSTEPS ( MOD, [CATALOG#], [STEP#], LAYER, MIN_OHMS,
    NOM_OHMS, MAX_OHMS, LEFT_PERCENT, RIGHT_PERCENT )
  SELECT tblSpecSteps.Mode, tblSpecSteps.[Catalog #], tblSpecSteps.[Step #], tblSpecSteps.Layer,
         tblSpecSteps.[Min Ohms], tblSpecSteps.[Nom Ohms], tblSpecSteps.[Max Ohms], 
  	   tblSpecSteps.[Left %], tblSpecSteps.[Right %]
  FROM tblSpecSteps LEFT JOIN PMSANGAL_TBLSPECSTEPS ON ((tblSpecSteps.[Catalog #] = 
    PMSANGAL_TBLSPECSTEPS.[CATALOG#]) AND  (tblSpecSteps.[Step #] = PMSANGAL_TBLSPECSTEPS.[STEP#]))
  WHERE (((PMSANGAL_TBLSPECSTEPS.[CATALOG#]) Is Null) AND ((PMSANGAL_TBLSPECSTEPS.[STEP#]) Is Null));
  

  You need an AND in the WHERE condition because all the primary key columns are not null.
• Repeat the above steps for all tables.
• If you have no primary key in a table, do a join on all columns and a null check on all columns using AND and not OR.
• Go to Macros.
• Select Action as open query, and select one of the append queries. The view will be datasheet,and the data mode will be edit.
• Save the macro.
• Select the macro, and go to Tools->Macro->Convert Macro to Visual Basic.
• Select both Add error handling and Comments if they are not already selected, and click on Convert, which converts the macro into a module with a function name.
• Save this module.
• Go to the module again, and modify it, using the following template:

  '------------------------------------------------------------
  ' CalMacro
  '
  '------------------------------------------------------------
  Function CalMacro()
  On Error GoTo CalMacro_Err
      
      Dim strConnect As String
      Dim strMyFile As String
      Dim dbs As Database
      Dim rst As Recordset
  
      strConnect = "ODBC;DATABASE=PUNEET;DSN=PUNEET;" & "UID=PMSANGAL;" & "PWD=********;"
  
  'PUNEET is the DSN or name of the database...
  
      Set dbs = OpenDatabase("", False, True, strConnect)
      Set rst = dbs.OpenRecordset("PMSANGAL.tblCal", dbOpenDynaset)
      
      DoCmd.SetWarnings False
      DoCmd.OpenQuery "tblCal Without Matching PMSANGAL_TBLCAL", acNormal, acEdit
      
      dbs.Close
  
  CalMacro_Exit:
      Exit Function
  
  CalMacro_Err:
      MsgBox Error$
      Resume CalMacro_Exit
  
  End Function
  

• Repeat the above procedure for all the append queries.
• After you have created the modules, you can delete the macros with which you created them.
• Again, create a new macro.
• Select Action as the RunCode, and select or type the function name from the module just created for it. To do this, double-click on Functions->Database and select the correct module and function.
• Do this procedure for all append queries in this same macro.
• Finally, select Action as Quit, with the options entry of Exit.
• Now a simple step, but the most important one: Make a batch file with the following information:

  "C:\Program Files\Microsoft Office\Office\Msaccess.exe" "database path" /x  Macroname
  

• Run the batch file.

Automated Method

The second migration method provides distinct advantages over the first.

Consider for example the task of datatype conversion. OMW provides a wizard that guides you through the process and simplifies migration. The tool recommends and provides default values that can be changed easily. This table lists some of the recommendations:

Access Field	Default	Recommended
Text	Varchar2(999)	Varchar2(999)
Number - long integer	Number(11,0)	Number(11,0)
Number - byte	Number(3,0)	Number(3,0)
Number - integer	Number(5,0)	Number(5,0)
Date/time	Date	Date
Boolean	Number(1,0)	Number(1,) or char(1)

Even for databases other than Access, OMW provides full support for conversion of triggers, procedures, and database schemas in an integrated environment. The versions of Microsoft Access that can be converted are 2.0, 95, 97, and 2000.

Here is a table that summarizes the conversions between Oracle and Access:

Feature	Conversion Supported?
Tables	Yes
Views	No (queries only)
Indices	Yes
Groups/Roles	N/A
Users	No
Constraints	Yes (validation rules)
Privileges	No
User-Defined Types	N/A
Stored Procedures	N/A
Triggers	N/A
Embedded SQL	N/A
Other Features	Relations, link tables, application code reuse

The following figure illustrates how the OMW works at architectural level:

Figure 1: OMW Architecture

• The Capture wizard captures the source database.
• The Migration wizard migrates the Source Model to the Oracle Model.
• Scripts create a set of files that describes the database structure, and they also copy data from the source database to the destination database.

To migrate different third-party databases, OMW uses plug-ins specific to each database it can migrate, including SQL Server, DB2, and Informix. At least one plug-in must be installed with OMW.

Oracle maintains extensive documentation on OTN about how to use OMW to perform a migration, and a free download is available. But the following synopsis implies how easy the process is:

• Start the Capture wizard.
• Click on Next.
• Enter the information required to connect to the destination Oracle database.
• Click on Next.
• The "Migrate Tablespaces, Users, and User Tables" page appears.
• Review the information on the "Migrate Tablespaces, Users, and User Tables" page, and click on Next; the Migrate Table Data page appears.
• Specify whether data should be transferred during this Migration wizard session.
• Click on Next; the "Select Schema Objects to Migrate" page appears.
• Select the schema objects to migrate, and click on Next; the Summary page appears.
• Review the summary information.
• Click on Finish to start the migration.
• A progress screen appears, displaying information about the migration's progress. The migration process can take a long time with complex databases.
• With the Microsoft Access plug-in, the Modify Microsoft Access Database screen appears. You can also modify the Microsoft Access database file so that Microsoft Access Forms and Reports can operate with the destination database, using an ODBC connection:
• Enter the user name for the ODBC connection. The ODBC connection user name is the name of the database being migrated.
• Enter the password for the user.
• Enter the name of the ODBC connection to the destination database.
• A progress screen appears, displaying information about the migration's progress. The migration process may take a long time with complex databases.

Conclusion

The steps here are just an overview of the process, which can be shorter or longer, depending on the database that needs to be converted. Using OMW is strongly recommended for migration. You would do well to take advantage of Oracle's online education and numerous forums on this topic. And for information about migrating your Access applications to HTML DB, read this Technical whitepaper.