Re: Access-to-Oracle

On 7 Jan 2002 12:56:55 -0800, astralwarrior_at_hotmail.com (Dan Barron) wrote:

>A common question I'm sure, but here goes...
>
>I'm an experienced DBA who is using Oracle for the first time. I have
>a couple of Access databases to convert and was wondering what you all
>think is the best way to accomplish this. The databases are Access
>2000 databases and my Oracle 8i version is 8.1.7.0.0.
>
>I read about a Migration Assistant, but it seemed limited Access 97
>plus I couldn't find it anywhere to download. Is the MA the best way?
>I could write an SQL script to create the tables, etc. and then export
>the data to text files and import, but that seems like a lot of work.
>I've tried the "Export" command in Access, but the results are not
>satisfactory (it seems very stupid about data types, for one thing.)
>
>Any help or pointers to information as to the best way to accomplish
>this would be much appreciated.
>
>dan
>
>Dan Barron
>ACP-ASIM
>dbarron_at_mail.acponline.org

I tried the Migration Assistant. While it was OK, I found it very confusing. I ended up rewrtiting table definitions myself, link to them in Access, and doing in INSERT INTO... SELECT * FROM... on all the tables. The latter part was slow (ODBC) but you could use a DTS package, and the time is almost nothing at all.

Writing the definitions yourself helps for other reasons. You get to learn more about Oracle, and get to name all constraints yourself, so they have intelligent names.

Brian Received on Tue Jan 08 2002 - 10:39:44 CST