Solved

Export tables from oracle to Microsoft access

Posted on 2011-03-15
12
688 Views
Last Modified: 2012-05-11
I have toad installed and have access to oracle tables that need to be exported to a local access db. Please explain step by step how it is to be done.
0
Comment
Question by:PearlJamFanatic
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 12

Expert Comment

by:enachemc
ID: 35135594
export the tables as SQL (it will generate a lot of simple insert instructions per file) and execute the files in access
0
 

Author Comment

by:PearlJamFanatic
ID: 35135649
how do i export table structure?
I have to create the tables as well in the blank access db
0
 
LVL 4

Expert Comment

by:MarioAlcaide
ID: 35135680
Hi, you can use this software:
http://www.spectralcore.com/fullconvert/tutorials/convert-oracle-to-access.php

You have to pay, but there is a trial version that maybe fits your needs.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

by:enachemc
ID: 35135691
In the schema browser, select tables. IN the Left Hand Side (LHS) select
the tables you want to create elsewhere. Right click and select "create
script". Fill in the options and OK.
0
 

Author Comment

by:PearlJamFanatic
ID: 35135733
enachemc:i did what you suggested. Now I have 2 .sql files. How do i run them in MS access
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35135841
1- first you need to create a duplicate Oracle table in Access.
2- you might need to change some of the datatype, as the datatype in Access is different from Oracle. run the script in Access.
3- export the Oracle data using Toad's Export option and choose export to CSV file format.
4- then import the data into Access. here is the steps to import data into Access using CSV files: http://www.brighthub.com/computing/windows-platform/articles/27511.aspx

0
 

Author Comment

by:PearlJamFanatic
ID: 35136024
OP_Zaharin:csv is not shown as an option when exporting from oracle. can you give the exact steps.
When i select .txt option it writes insert commands for each row
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35136032
whats the Toad version are u using?
0
 

Author Comment

by:PearlJamFanatic
ID: 35136046
9.0.1.8
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35136052
0
 

Author Comment

by:PearlJamFanatic
ID: 35136079
There is no export wizard option within toad
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 500 total points
ID: 35136107
try this: select the table from the schema browser and right click, choose save as then choose CSV.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
report returning null 21 96
grouping on time windows 6 51
oracle 11g 23 83
Oracle Listener Not Starting 11 44
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question