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

anyone has solution ??? Please help

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,

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

anyone has any advises???

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.