Consolidation is the key driver for Migration from Microsoft Access to Oracle Application Express

I have just been reviewing the survey results and feedback received from early adopters of the Application Migration Workshop a new feature of Oracle Application Express to assist with migration of Microsoft Access Forms and Reports to Oracle Application Express.

Looking at the data, consolidation is the key reason. In fact, it was twice as popular as the next given reasons of company direction and performance.

So what does consolidation really mean in this context. It is 1 to 1, i.e. one Access database to a unique schema in Oracle or is it a many to 1, removing duplication and increasing the shared access to a "single source of truth". I think it is a mixture of both.

In talking with customers over the last year or so, some customers have told me that they have thousands of access applications and refer to it as their "access problem". Clearly it would be a stretch to think that they have thousands of unique applications, so there must be a lot of duplication and redundant access databases.

So how do you define an appropriate access migration strategy. Well I believe firstly, you need more information about your access databases. This is really an assessment phase. In doing an assessment of a large number of access databases, I would split it into two parts:

1. Technical Assessment
2. Business Assessment

Technical Assessment

In the technical assessment phase, you are trying to build up some key data about these access databases. I refer to it as a technical assessment, as hopefully you could have a utility to collect this information. Data to collect would include:

• Name, Owner, Size
• Date created, Date last modified
• Version
• # of Tables, Forms, Reports, Queries, Pages, Modules,
• Links
• links to other mdb files
• linked data e.g. link tables via ODBC to Oracle, SQL Server, ...
  
• Location (server)
  
• ...

Business Assessment (Business Level Discovery)

This requires human involvement, and is critical for a subsequent analysis phase. Data points here would include:

• Business Owner (by department, group)
• Description (purpose)
• Application Type (data entry, reporting, ...)
  
• Priority (Business Critical to Redundant)
• Action (migrate, ignore, duplicate, remove, ...)
• # of users
  

I can see this being the basis for a nice Oracle Application Express Application, that would support the data entry of business level data, and the generation of useful reports and charts. This would then facilitate the next level of analysis and allow you to ask some interesting questions:

• What is my spread of versions of Microsoft Access?
• How many copies of project.mdb or tasklist.mdb do I have? :-)
• What's my largest / average access database size?
• Which access databases are most heavily used?
  
• ...

Now I think that would be a very useful data collection utility and Oracle APEX application.