1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Access To Oracle

Discussion in 'HTML, Graphics & Programming' started by MoFish, 13 Jan 2006.

  1. MoFish

    Gangster

    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: 13 Jan 2006
  2. happytechie

    Soldato

    Joined: 18 Oct 2002

    Posts: 5,601

    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
     
  3. MoFish

    Gangster

    Joined: 29 May 2005

    Posts: 144

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

    MoFish
     
  4. happytechie

    Soldato

    Joined: 18 Oct 2002

    Posts: 5,601

    Location: Surrey

    The easiest way to do it will be to export from the access in CSV format and then write an SQL Loader script to import it into Orable.

    HT
     
  5. MoFish

    Gangster

    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: 13 Jan 2006
  6. roboffer

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,599

    Location: Sunderland

    Is there nothing like like DTS for Oracle?
     
  7. Haircut

    Mobster

    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?
     
  8. MoFish

    Gangster

    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

    [​IMG]

    Relationships

    [​IMG]

    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
     
  9. Haircut

    Mobster

    Joined: 18 Oct 2002

    Posts: 3,926

    Location: SW London

    Have you thought about using MSDE?
    http://www.microsoft.com/sql/editions/express/default.mspx

    It will be easier to transition the change to this than to Oracle, plus it's free.
    I don't know how much you have looked into this, but Oracle is pretty expensive if you aren't using it for personal use
     
  10. MoFish

    Gangster

    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.
     
  11. Haircut

    Mobster

    Joined: 18 Oct 2002

    Posts: 3,926

    Location: SW London

    Ah, it makes sense to use Oracle then.

    As far as doing the loading, I would use SQL*Loader as happytechie suggested.
    It's the quickest way to load lots of data into Oracle tables.

    Here is a guide on how to use it
    http://www.orafaq.com/faqloadr.htm
     
  12. MoFish

    Gangster

    Joined: 29 May 2005

    Posts: 144

    thank you, ill have a read.

    MoFish
     
  13. Hades

    Caporegime

    Joined: 19 Oct 2002

    Posts: 25,643

    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.
     
  14. MoFish

    Gangster

    Joined: 29 May 2005

    Posts: 144

    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