Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2003
    Posts
    9

    Unanswered: How to insert data from an access table into Oracle table automatically

    Hi all,

    I have a table in access and I would like to get the data in this table to insert into another table in Oracle automatically.

    Is there anyway I can tell Access to export data into Oracle automatically or give Oracle the path so it can just go to a folder where I have the access table to get the data to insert?

    I would greatly appreciate your response.

    Thank you very much!


    -Bryan

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    1) With MS ODBC Administrator, create a data source entry for the Oracle database in ODBC

    2) Link both tables into Access:

    File>Open>... and select local Access DB
    Then:
    File>Get External Data>Files of type>ODBC Databases()
    Select data source
    Select table

    3) Insert data from one table into the other:

    View>Database Objects>Queries>Design
    Select Both Tables
    Select some column from both tables (PK?)
    View>SQL View

    Code your insert statement.
    Run!
    Commit

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Posts
    9
    Thank you so much for your reply. But with this, I have to do it manually.

    Is there anyway I can have the inserting job done automatically everyday at a specific time!? For example, I want the data in access to be inserted in Oracle at midnight every night.

    Please advise me on how to do that. I really appreciate your help. Thanks!


    - Bryan



    Originally posted by LKBrwn_DBA
    1) With MS ODBC Administrator, create a data source entry for the Oracle database in ODBC

    2) Link both tables into Access:

    File>Open>... and select local Access DB
    Then:
    File>Get External Data>Files of type>ODBC Databases()
    Select data source
    Select table

    3) Insert data from one table into the other:

    View>Database Objects>Queries>Design
    Select Both Tables
    Select some column from both tables (PK?)
    View>SQL View

    Code your insert statement.
    Run!
    Commit


  4. #4
    Join Date
    Aug 2003
    Posts
    328
    Try sql loader. If you want to do it automatically you can make an application in SQL Plus and run the script as a scheduled task. I did it this way and it works like a charm.
    Last edited by exdter; 08-25-03 at 16:30.

  5. #5
    Join Date
    Aug 2003
    Posts
    9
    exdter,

    Thanks for your input.

    I am very new to Oracle and have no clue about SQL loader.

    How do you make an application in SQL Plus? Is there anyway I can give SQL script the path to read my data in Access?

    Please give me some inputs. I really appreciate your help.

    Thanks in advance.


    -Bryan

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Also you could try the Oracle Migration Workbench:

    http://technet.oracle.com/software/t...h/content.html

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Aug 2003
    Posts
    328

    SQL loader

    Go to this link. http://www.csis.gvsu.edu/GeneralInfo.../ch04.htm#1656

    Its a bit too long to explain here. I learned it from a book in no time.

    Originally posted by BryanD
    exdter,

    Thanks for your input.

    I am very new to Oracle and have no clue about SQL loader.

    How do you make an application in SQL Plus? Is there anyway I can give SQL script the path to read my data in Access?

    Please give me some inputs. I really appreciate your help.

    Thanks in advance.


    -Bryan

  8. #8
    Join Date
    Aug 2003
    Posts
    328
    Your SQL Plus Application will use a simple insert command into the Oracle table. Open a text file and put the code and have the task scheduler open that text file to run it in Oracle.



    Originally posted by BryanD
    exdter,

    Thanks for your input.

    I am very new to Oracle and have no clue about SQL loader.

    How do you make an application in SQL Plus? Is there anyway I can give SQL script the path to read my data in Access?

    Please give me some inputs. I really appreciate your help.

    Thanks in advance.


    -Bryan

  9. #9
    Join Date
    Aug 2003
    Posts
    9
    Exdter,

    Thank you so much for your help so far.

    I can write the scheduler in Unix and schedule it to run at the time I want, but I dont know how to write a SQL Plus statement to go to a specific folder where I store my access database to read the data.

    Would you please help me on this!? I really appreciate your help.

    Thanks in advance!


    -Bryan




    Originally posted by exdter
    Your SQL Plus Application will use a simple insert command into the Oracle table. Open a text file and put the code and have the task scheduler open that text file to run it in Oracle.

  10. #10
    Join Date
    Aug 2003
    Posts
    328
    Make a text file with your insert query, for example: insert into [table] select * from [table]. Save the file as my_file.txt or my_file.sql, it doesn't matter.
    You can use the SQLPlus @ or start command (they are the same) and make a path to that file in your scheduler. For example: @ c:\my_file.txt
    This will open and run your text file.
    Good luck.

  11. #11
    Join Date
    Aug 2003
    Posts
    9
    exdter,

    Are you sure it is gonna work ?

    My scheduler is a ksh file. Is there a way tell a ksh file to go to a specific folder to run a file?

    I remember that once you told me to use SQL loader to load my data from access database into Oracle. How could you do that? That may work for a text file but it does not regconize a path to go to a folder and read my access database data.

    Would you please help me a little bit more in details about this? I really appreciate your help.

    Thanks in advance.


    -Bryan

  12. #12
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by BryanD
    exdter,

    Are you sure it is gonna work ?

    My scheduler is a ksh file. Is there a way tell a ksh file to go to a specific folder to run a file?

    I remember that once you told me to use SQL loader to load my data from access database into Oracle. How could you do that? That may work for a text file but it does not regconize a path to go to a folder and read my access database data.

    Would you please help me a little bit more in details about this? I really appreciate your help.

    Thanks in advance.

    -Bryan
    You can't read Access tables directly from Oracle. But to run the data into Oracle from Access is easy.

    We'll start assumption you have a Windows PC that can stay logged on 24/7.
    1. Create an ODBC call to the database you want to insert data into.
    2. In the Access DB create a link to the table that you want to insert data into. Save the password. Note that you may have to give the database an index to make the linked table able to take insert/updates.
      Once you have a table you can insert into....
    3. Create an insert query with whatever criteria you want to use.
    4. Create a form in Access. Go into design mode
    5. Under the "On Open" or "On Load" click it to [Event Procedure]
    6. Hit the button to get into the code area. Type in
      Docmd.OpenQuery "<queryname>".
      Docmd.Exit
    7. Go to Tools -> Startup -> Display Form
    8. Select the form that you just built
    9. Save and close Access
    10. On the PC set up your task scheduler to open the database every day at midnight.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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