Importing Access applications into Apex

Some time ago I’ve been experimenting with the Apex 3.0 feature that migrates entire Access applications to Application Express. It took me a lot of time because I found out the path to it is winding and bumpy. I would like to share some of my issues here. A very good tutorial is found at Oracle’s OTN site (which I partially used).
For the experiment, I used a real-life Access database, so not the straightforward Northwind database that Microsoft supplies with Access. Most of the steps are done with SQL Developer 1.2. I used Apex 3.0. 

I downloaded and re-installed v1.2 because I had version 1.0 and installing the updates and OWMB plugins failed with an error. With the new version of SQL Developer the migration has become a lot easier: most steps can now be performed from within SQL Developer instead of switching between OMWB, Apex and SQL Developer until you go berserk. Also, there is a hint of a workflow in SQL Developer.

Some of the issues I encountered:
[a] One of my Access tables had two columns named ‘0’ and ‘1’. The workbench translated both column names to ‘A’, which would give a duplicate column error. I manually renamed the columns to unique and meaningful names.
[b] Imported tables are often not valid in the Application Migrations in Apex. That is because they need a primary key as explained above. The thing I noticed was that primary keys with multiple columns resulted in an “invalid identifier” exception. It appears an sql script is generated where the second column is named “0”. This must be a bug, which can be worked around by adding the primary key manually. The table definition will be valid then.
Don’t expect that all the forms created in Access will result in nice functional Apex forms. In my case, the result was utterly, utterly useless.
[d] Access contains lots of queries; they will be converted to views. You guessed it, they were not very helpful for me, if they compiled at all.
[e] Lat but not least; all logic in Access is in VB. This will not be converted other than that it’s commented out and inserted into a pl/sql block. Ah well, you were planning to rebuild the forms and reports anyway, weren’t you? So why not do the logic on the go 😉

My reccomendation: use this tool only for data migration. Recreate the datamodel with proper foreign keys and naming and trash the conversion tables and views. Forget the forms, the views, the queries and build everything from scratch. Trust me, it’ll save you time and frustration.