Re: Tool to move structure AND data from Access to Oracle?

From: Corey Lawson <corey.lawson_at_worldnet.att.com>
Date: Wed, 19 Jan 2000 02:01:25 -0600
Message-ID: <863rdv$hb4$1@bgtnsc01.worldnet.att.net>

In my experience, it doesn't do the best job at migrating data to Oracle from Access, frequently bombing for unapparant reasons, and sometimes not pushing the complete data set from Access to Oracle.

However... the script files it makes are pretty much invaluable. I usually hack these by hand to work the way I want them to, or to sort of "batch mode" any changes I knew I wanted to do en masse from the GUI but didn't want to mouse through to do...

One side effect from it is that if you install it on an otherwise Oracle Client-free system, you also install the Oracle Client software (no choice in the matter).

After that, it's easier and faster to link the newly created Oracle tables to the same Access DB, and before turning on any triggers or referential integrity on the Oracle side, do some code like this:

public sub PushDataToOracle
dim t as table
dim db as database
dim OraSchema as string
dim insertSQL as string
dim q as querydef

set db = dbengine(0)(0)
set q = db.createquerydef("") 'create a temporary querydef object.

for each t in db.tabledefs
'linked tables will be named in Access thusly: ' SCHEMA_TABLENAME
if left$(t.name) = OraSchema then

        sql = "select * from " & mid$(t.name, len(oraschema)+2) _
          " into " & t.name & ";"
        q.sql = sql
        q.execute

end if
next t

end sub

then run it from the Immediate window in Access...

...and, finally, set your triggers and referential integrity on the Oracle side by hand.
"Mike Carter" <mike_at_delriotech.com> wrote in message news:Op6h4.32786$475.870903_at_news4.giganews.com...
> However, this utility is still "in developement" for 8i.
>
> Oracle Enterprise Internet Tools Product Management
> <no_replies_at_uk.oracle.com> wrote in message
> news:85vvoo$jpq$1_at_inet16.us.oracle.com...
> > Oracle ship an Access migration utility with Oracle 8
> >
> > --
> > Regards
> >
> > Jason
> >
> >
>

> > __
> >
> > Jason Pepper - Enterprise Internet Tools Product Management
> >
>

> > __
> >
> > Opinions are mine and do not necessarily reflect those of Oracle
> Corporation
> >
> > Todd Owers <toddowers_at_my-deja.com> wrote in message
> > news:85dovm$ubp$1_at_nnrp1.deja.com...
> > > Is there a tool that will move the structure AND data from an Access
> > > database to Oracle?
> > >
> > > Erwin, DBArtisan, etc. will move the structure, but not the data.
> > >
> > > Thanks in advance for your help.
> > >
> > > Todd Owers
> > > toddo_at_gcr1.com
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
>
Received on Wed Jan 19 2000 - 02:01:25 CST