Access To Oracle

MoFish · 13 Jan 2006 at 01:23

Hello.

I currently have a large database consisting of about 5000 records. What im trying to achive is a simple conversion from the access database to an oracle database. I was wondering if anyone knew if a SQL query exists to perform the following, or if this can be achived. Im indending on writing software, which would do the conversion for me eventually, but a SQL query would be a start.

Any feedback welcome.

Thanks MoFish

MoFish · 13 Jan 2006 at 12:50

Just migrate all the data from the access database into an oracle database.

MoFish

MoFish · 13 Jan 2006 at 15:28

interesting, never really done anything of this sort before, however im convinced I can achive it some way or another. i'll try the exporting of it into CSV initially.

*guessing something like* select * into text `file.csv` from `mytable`

thanks happy techie - mofish

MoFish · 13 Jan 2006 at 19:05

sorry i just used 5000 as a rounded number to get my problem accross. my database is large and will proberly grow over the next few years. i'm concerned how access is going to cope with this overhaul of the database in the future, so was recomended to shift to oracle.

Tables

Relationships

Some of the tables consist of around 125000 entrys, others are smaller on scale.Today I have successfuly managed to implement a convertor to convert the tables into CSV format, but am unsure where or what to do from here really.

Thanks Again - MoFish

MoFish · 13 Jan 2006 at 19:40

we already have oracle setup elsewhere in the firm, so isnt a problem on the cost side of things. Achiving the migration/transformation to oracle is where our problem lies.

MoFish · 13 Jan 2006 at 20:07

thank you, ill have a read.

MoFish

MoFish · 14 Jan 2006 at 12:03

Hades said:
I've done this exact thing. I evetually wrote a simple Java program to do it but before I bothered with that I used SQL from within Access. You need to add the Oracle table into Access as a link table using the ODBC driver. Once both the access and Oracle table are there then you just need to write a basic query to select everything from Access and insert it into Oracle.

Sorry I cam't be more specific but hopefully that should give an idea.

perfect, you seem to have done the exact thing i require to do. I'll open up access and try to add the oracle database via a link table as suggested.

Thanks Again - MoFish