Re: Migrate Data from MSAccess to Oracle

From: Glen A Stromquist <glenstr_at_no_spam_canada.com>
Date: Fri, 24 Sep 2004 18:48:22 GMT
Message-ID: <WZZ4d.82818$KU5.4523@edtnps89>

Praveen wrote:

> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<KvOdnc2pgJvgQMzcRVn-hQ_at_comcast.com>...
>

>>"Greg Forestieri" <gforestieri9_at_yahoo.com> wrote in message
>>news:6a8cdd95.0409221114.133d7e12_at_posting.google.com...
>>| spraveen2001_at_yahoo.com (Praveen) wrote in message
>> news:<98d8ec76.0409220553.36659419_at_posting.google.com>...
>>| > Hi All,
>>| >
>>| > We have an application in french language at client site which is
>>| > using MS-Access as DB. Now we redesigned the application to web based
>>| > using java and Oracle as the backend DB. Now they want to load data in
>>| > MS-Access db to oracle db. I'm using UTF8 db in oracle. Oracle DB and
>>| > MSAccess DB are not much syncronized with tables/columns. Can anyone
>>| > tell me how can i approach for data migration?
>>| >
>>| > Thanks in advance,
>>| > Praveen
>>|
>>|
>>| Look into "ODBC"
>>
>>at some point oracle had an MS-Access Migration Assistant, but I'm not sure
>>if that is still around or how good it is/was
>>
>>however, as Greg mentioned, ODBC is a very good option -- you can attach
>>Oracle tables to MS-Access and then use simple Access append queries to copy
>>(insert) access data into Oracle tables
>>
>>i would suggest creating oracle tables that are a mirror image of each
>>access table and just move the data as is, then use PL/SQL procedures to
>>transform these 'staging' tables to the production tables
>>
>>++ mcs

>
>
> Thanks All for the response,
>
> As Greg mentioned using ODBC and attaching oracle tables to MS-Access
> is good option. I think for this we may have to write Macro in
> MS-Access DB to load into oracle table. as table/column in both the
> dbs' are not fully synchronized. But, client need a standalone program
> which takes the data from access mdb file and load into oracle tables.
> While loading we may have to handle different conditions. So, i think
> creating oracle tables that are a mirror image of each
> access table and just move the data as is, then use PL/SQL procedures
> to
> transform these 'staging' tables to the production tables will be
> helpfull.
>
> Thanks,
> Praveen

You could also perhaps attach the ms access tables to oracle via dblink using heterogeneous services thru odbc, that may or may not work better for your needs. This lets you access the "access" table from the oracle side, instead of the other way around when you link oracle tables to msaccess

just another suggestion... Received on Fri Sep 24 2004 - 13:48:22 CDT