Access To Oracle

Associate
Joined
29 May 2005
Posts
144
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
 
Last edited:
Soldato
Joined
18 Oct 2002
Posts
5,600
Location
Surrey
what do you want to do? transition the access fornt end to use an oracle back end and migrate the data, rebuild the access front end in another tool (like oracle forms) and migrate the data or just migrate the data?

HT
 
Associate
OP
Joined
29 May 2005
Posts
144
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
 
Last edited:
Soldato
Joined
18 Oct 2002
Posts
3,926
Location
SW London
I thought there was an option to export the data to another ODBC database from within Access?
If not then you can always export to CSV files.

Is it just one table of 5000 rows we're talking about here?
If so then what's the need to use Oracle - it seems a bit of overkill for something so small?
 
Associate
OP
Joined
29 May 2005
Posts
144
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

db.JPG


Relationships

db1.JPG


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
 
Associate
OP
Joined
29 May 2005
Posts
144
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.
 
Man of Honour
Joined
19 Oct 2002
Posts
29,518
Location
Surrey
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.
 
Associate
OP
Joined
29 May 2005
Posts
144
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
 
Back
Top Bottom