Tuesday 12 March 2013

AS/400 to oracle (via msaccess) using Oracle SQL Developer. Data conversion

I loaded msaccess with some bespoke tables so that I could copy this to oracle, but how…

Loading is simple, just import data, choose ODBC. Make sure that you create a system naming convention ODBC data source so that access can see the list of tables.

Use SQLDeveloper 3+! Do not use the older versions, they do not have the options that you need for granular table selection.

Get it from here

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Make sure that you have jdk 1_06.35, or it ain’t going to work…

Expand the zip contents and run the sqldeveloper.exe bin in the \sqldeveloper\bin dir (der!)

Try a connection from SQLDeveloper to your access database

clip_image002

clip_image004

If you get “No read Access to system Tables. Modify Access db before retrying” – you’ll need to change the DB permissions and options.

Now alter the permissions for your database.

I did this in 2000

First, open the DB and goto tools-options and ensure system objects is checked

Then goto tools->security->user and group permissions

Choose users and users radio button and then give (all) appropriate users “read data” and “read design”. Do this for all Msys* tbales.

clip_image006

See above

Do that for all 5 system tables and then do the same for the groups (this is what I did).

Exit the DB

Then do the connection again via SQL Developer

clip_image008

Terrific!

You see your tables and access databases listed.

You select your tables in access and right click and choose copy.

Make sure you have an oracle connection defined as the owner of the schema that you want the data in (in my case, PRODDTA).

1 comment:

Richard Drouillard said...

My organization get periodic data deliveries from a government agency in the form of access databases. When I demoed this functionality to the people that load the data earlier this year, I blew their minds with how easy it was. Plus, since the mdb is always in the exact same format and file name, they just have to drop it in a pre-defined place and SQL Developer already knows all about it.