Re: Migrating Access Databases into Oracle

Michael Kuenzi wrote:
>
> We are going to migrate our MS Access databases to Oracle 7.3. The first
> step is to make a reverse engineering with ERwin (Access to ERwin). So
> we can create SQL scripts for Oracle. This makes it possible to migrate
> the tables into Oracle and let the GUI in Access.
>
> My Problem is:
> In Access there are automatic counters for primary keys (surrogate
> keys?!). ERwin changes this to ROWID for Oracle. IMHO it is not correct
> to take the ROWID as a primary key. Is there another way to migrate? Has
> anyone been successful? What about sequences together with Access?
>
> Thanks
>
> Michael
> _________________________________________________________________
>
> Snail mail : Michael Kuenzi Phone : +41 1 977 1232
> Dipl. El. Ing. HTL Fax : +41 1 977 1234
> Schopfhaldenstr.15
> CH-8340 Hinwil
>
> Internet : mkue_at_active.ch
> _________________________________________________________________
Hi Miachael,

Oracle 7.3. has a migration tool as everybody told me, when I describe the way we migrated. We also used an reverse engineering tool. To emulate counters you've got to modify pk-fields to number. Then create for each table a sequence and a before-insert-trigger which will fetch a new sequence-number and automatically numbers the the new record with the sequence number.
While populating the empty oracle with the values of your Access-tables disable your triggers or create the sequences and triggers after populating with a start value.

create Sequence SEQ_AnschriftenID start with 1200;

create or replace trigger tI_Anschriften before INSERT on Anschriften for each row
begin

        Select SEQ_AnschriftenID.NEXTVAL into :new.ID from dual; end;
/

-- 
Regards

Matthias Gresz    :-)