convert access to Oracle with Memo column in Access
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: convert access to Oracle with Memo column in Access

Hybrid View

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    convert access to Oracle with Memo column in Access

    Hi all,

    what is the best way to convert Microsoft Access to Oracle with MEMO column in access???

    Could someone please give me some details of what need to be done ???

    Thanks

  2. #2
    Join Date
    Sep 2002
    Posts
    411
    anyone has solution ??? Please help

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    it's not difficult.
    Create an ODBC data source for the Oracle database.
    Inside Access, highlite the table, choose File - Save As/Export - External File or Database.
    In the "File Type" listbox select "ODBC Databases" (very bottom line).
    Write down name of the table you want to create in Oracle, select the ODBC source you created.
    If prompted, write name/password and confirm.
    That's all.

    Don't forget that Access creates the table with name exactly as you write it, without conversion tu uppercase. If you write "test" then you'll have to name it "test" in sql. E.g. select * from "test".

    With this scenario, memo field is converted to LONG.
    If you don't want that, you can pre-create the table with CLOB or VARCHAR2 datatype, connect it into Access and perform "insert into the_connected_table select * from my_access_table". Works with both CLOB and VARCHAR2 datatypes.

    Good luck!
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  4. #4
    Join Date
    Sep 2002
    Posts
    411
    aLES,

    Thanks for your reply. I did follow your instructions but I have some problems.

    1. I have the ODBC created for the database
    2. you said go to SAVE AS/EXPORT-EXTERNAL FILE or database--- which one am I suppose to go to??? SAVE AS and EXPORT are two different option (not one) . If I go to SAVE AS I have something like SAVE AS query and the query name. If I go to export and scroll down to the ODBC pick the database and put the password in and I got the following error:

    ODBC failed:
    ORA-01754: table may only contain one column of the type long


    per your instruction, you said I can precreate the table in Oracle and do an insert, can you elaborate a little more on this.

    Thanks

  5. #5
    Join Date
    Sep 2002
    Posts
    411
    anyone has any advises???

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    The message you got is clear, I think. You have to convert the memos to CLOBs.
    Lets say you have a table T in Access:
    Code:
    id    number
    memo1 memo
    memo2 memo
    memo3 memo
    Create a table in the Oracle DB:
    Code:
    create table test (
    id    number,
    memo1 clob,
    memo2 clob,
    memo3 clob)
    (I don't have english Office, I must do backward translation and it is not perfect)

    First, you have to connect the Oracle TEST table to Access:
    In the Tables folder choose New, then "Connected table Wizard" (bottom line) then ODBC databases again. Find the TEST table in the listbox and confirm.

    Make an insert in the Queries folder:
    Code:
    insert into owner_test(id, memo1, memo2, memo3)
    select id, memo1, memo2, memo3 from T;
    Works in Access97.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width