Migration

[RamHardikar]

Hi,

Could someone guide me on how to proceed for migrating MS Access data (including table, indexes etc) to Oracle? What ground work needs to be done for this? What should be the approach for this?

Thanks

 

[dbtoo2001]

http://otn.oracle.com/pls/db92/db92.show_toc?partno=a97262&remark=drilldown&word=microsoft+access

 

[33216CLC]

RamHardikar,

I want to do the same thing. I am so frustrated with access.
I just want to know if you have completed the migration and how long it took, before I try the link.

Thanks.

 

[ddiamond]

33216CLC,

I wasn't able to see that link, so I don't know what it suggests, but I have successfully migrated many access 97 databases to oracle 8.0. Here is the approach I took:

Part I - migrating the access database to oracle
1. Create an oracle ODBC DSN.
2. Use MS Access export utility to export all of your tables to your oracle DSN (you will need to do this one table at a time).
3. Verify that all of the oracle tables that you exported have the correct stucture. You may have to use alter table statements to tweak some of the datatypes or data sizes. If any auto-counter columns were used in ms access, you will need to simulate this with an oracle trigger and sequence.
4. Manually add any referential integrity to oracle.

Part II - Migrating your applications from your access database to oracle:

1. Create a new MS Access Database with linked tables pointing to all of your oracle tables.
2. Point your applications to this new access database. At this point they will be using a mixture of the oracle engine and ms access jet, but they should behave correctly.
3. Gradually, query by query, rewrite them so that they can hit oracle directly. There will be some syntax differences. You don't need to migrate all of your queries at one time. Some of them can be pointing to the access database with linked tables while others hit oracle directly.
4. After you have successfully modified all of your queries so that they are pointing directly at oracle, you can delete the access database with linked tables. That was just used to ease debugging by allowing you to gradually move your queries off of access. Without it, you would have been forced to migrate them all at the same time.

 

[33216CLC]

Thanks ddiamond,

Excellent suggestions. My concern is mostly with rewriting or modifying the queries. I plan to do what u suggested, I just do not know the syntax. I have been researching for some tips and found some, but it's all still blurry for me...... I have always worked with Access in VB.

 

[ddiamond]

What version or Oracle are you using? Versions 9 and up do not have as many sql differences, but Oracle 8 does have a fair number. Here are the main differences I've encountered:

- use single quotes instead of double quotes.
- use double quotes in place of square brackets when your alias contains spaces
- use the % wild card instead of * when using the like operator.
- All joins must be done in the where clause. Oracle 8 does not support the join key word or syntax.
- use the (+) operator to indicate a left join in your where clause. For example: where a.field = b.field (+)

And of course many of the scalar functions are different:
- use substr instead of mid$ or Left$
- use decode instead of IIF

For a complete list you'll need a good reference manual. I recommend Oracle 8 - The Complete Reference from Oracle Press.

 

[33216CLC]

To tell u the truth, I do not have any version. I have decided to migrate, but have not as yet. According to what u said, it would be best to use 9. Thanks for rhe tips.