Converting from Access to Oracle

A question from Askville:

Can anyone provide any high level tasks, details etc. surrounding the steps that one would take to plan, execute and test a data conversion/move from a Microsoft Access database to an Oracle database?

Access databases store both data and user interfaces (forms, queries, reports, etc.) Oracle stores only data; user interfaces will have to be developed anew (there is, however, a possibility to recycle the user interfaces from your current Access database by replacing its current tables with attached Oracle tables exposed via ODBC). So some of the steps are:

  • Decide how you want to go about developing the new interfaces. Do you want to recycle your Access user interfaces as mentioned above? Or do you want your users to access Oracle data via a lightweight Windows client program with a look and feel similar to your current Access application? Would a Java program be OK? Or do you want a Web-based interface? If so, what’s your preferred development platform? (Oracle has a Web application development tool called HTML DB; additionally, every major scripting language out there, including ASP/ASP.Net, JSP, PHP, and Python, integrates with Oracle fairly well)
  • Decide if you want to retain the schema from the Access application or develop new ones. Quite often, Access developers have a poor understanding of normalization, so they come up with data architecture that is easy for a human being to understand, but hard for a computer to sift through and stores a lot of duplicate data.
  • Create a staging environment (buy a server or select an existing machine to work as the Oracle server, install and configure Oracle and its administration tools).
  • Migrate data from Access to the Oracle staging environment (which may involve transformation of data from the poorly-normalized Access schema to well-normalized Oracle schema).
  • Develop and test the user interface (meanwhile, the users are still working with the old Access application, adding and modifying data).
  • Create a production environment and configure the user interface to work with it.
  • Stop user access to the Access application and migrate all data from Access to the Oracle production environment.

If the above sounds confusing, you should seek professional help.

Leave a Reply

Your email address will not be published. Required fields are marked *