Ways to import Microsoft Access database into Oracle

[kimhoong79]

I am a newbie in Oracle and I am trying to figure out the ways to import Microsoft Access database into Oracle.

I have identified a few problems as Microsoft Access consists of Query, Form, Report and etc. Let's say I just want to focus on the tables only. What are the ways to do it?

At first, I thought it's a common task but after hours of searching in the Internet, I found very little information about it. What I got the most are software to do it.

Thanks in advance for those who are trying to help.

 

[SantaMufasa]

Kim,

Oracle can easily load delimited flat files, such as comma-separated values (.csv), or tab-separated values, et cetera.

Once you have such a file, you can use either Oracle's SQL*Loader utility, or you can even access the flat file directly as an "external table". Here is an example of flat, delimited DEPARTMENT data, which I can access directly as an "external table" in Oracle:

Following is text output written to file "DeptInfo.txt"

"10","Finance","1"
"31","Sales","1"
"32","Sales","2"
"33","Sales","3"
"34","Sales","4"
"35","Sales","5"
"41","Operations","1"
"42","Operations","2"
"43","Operations","3"
"44","Operations","4"
"45","Operations","5"
"50","Administration","1"

SQL> create directory kim_directory as 'c:\dhunt\sqldba\'
  2  /

Directory created.

SQL> create table department_csv
  2   (ID   number
  3   ,Name   varchar2(50)
  4   ,Region_id  varchar2(10)
  5   )
  6  organization external
  7  (  type oracle_loader
  8     default directory kim_directory
  9     access parameters
 10     (records delimited by newline
 11      fields terminated by ',' enclosed by '"'
 12     )
 13  location ('DeptInfo.txt')
 14  )
 15  reject limit unlimited;

Table created.

SQL> select * from department_csv;

        ID NAME            REGION_ID
---------- --------------- ----------
        10 Finance         1
        31 Sales           1
        32 Sales           2
        33 Sales           3
        34 Sales           4
        35 Sales           5
        41 Operations      1
        42 Operations      2
        43 Operations      3
        44 Operations      4
        45 Operations      5
        50 Administration  1

12 rows selected.

You can also access ACCESS tables from Oracle via ODBC (Open Database Connectivity) definitions, but that might be more hassle than it's worth, depending upon your need/application.

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kimhoong79]

Current solutions that I have in mind is by using the Enterprise Manager in the Express Edition (the only version I install in my home PC). The only drawback is it only supports text files, excel files (or .csv) and xml files.

Can you simply show me how to use ODBC to complete the task?

My problem is how import .mdf file into Oracle environment.

 

[Thargy]

kim,

oracle migration workbench has an access migration tool which will do precisely what you want.

If you want forms brought in, I believe that the lates version of Application Express has an excel and access importing routine. Have a look at the oracle documentation on-line for more details.

Regards

Tharg

Grinding away at things Oracular

 

[kimhoong79]

Tharg,

I believe it is the best option available to me. I will definitely take a closer look at it. Thanks for your help.

 

[oradba101]

Hi,

One option is Oracle Application Express which is supposed to convert an Access app over to an Oracle Application Express one.

Regards,


William Chadbourne
Oracle DBA