none
How to copy data from Oracle to Access table RRS feed

  • Question

  •  

    Hello to all.

    I need to code the following:

    1.Connect to an Oracle database.

    2.Execute a query.

    3.Insert the query result into an Access database table.

    I’m able to solve points 1 and 2. Can someone please help me how to code point 3?

    Thanks a lot in advance for any kind help.

    Octavio


    • Edited by octeixeira Thursday, March 1, 2012 10:10 AM
    Thursday, March 1, 2012 10:09 AM

Answers

  • There is no function which says, 

    AccessDataBase = Oracle DataBase.

    But you can update the database like if it was done by an end user by:

    1. Create a new datatable
    2. Fill that with a Dataadapter from Access using a where clause and an OleDBDataAdapter
    3. Loop through the retrieved datatable from Oracle and update row by row the Access Datatable
    4. Update the DataBase using again the OleDBDataAdapter

    Quite simple and straight foreward in my perception.


    Success
    Cor

    Thursday, March 1, 2012 1:52 PM
  • It can be done directly using a single SQL statement. The syntax will differ depending upon whether the Access table already exists. The below example assumes that it does exist and the column names are the same. If the column names are different they will likely need to be specified:

           Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\Test Files\db1 XP.mdb;")
            AccessConn.Open()
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [LOG_TABLE] SELECT * FROM [SCHEMA_NAME.LOG_TABLE] IN '' [ODBC;Driver={Microsoft ODBC For Oracle};Server=servername;Uid=username;Pwd=password;] WHERE LOG_DATE_TIME > ?", AccessConn)
            Dim DateCriteria As Date = #9/30/2011#
            AccessCommand.Parameters.AddWithValue("DateVal", DateCriteria)
            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, March 1, 2012 3:17 PM

All replies

  • I don't know Oracle well, but if it can attach an Access database like SQL server can, and if it has physical access to it, you can attach it and write an SQL that inserts the data (INSERT INTO or SELECT INTO), i.e. selects the data from the oracle db and inserts it into Access in one go.

    Armin


    Thursday, March 1, 2012 12:22 PM
  • Armin, it's possible with sqlServer, but if the task is in Access, might not work.

    Perhaps fill a reader and do a loop through, and writing each record.  That would allow converting the different data types.

    I used to do that type of work years ago, from MySQL to Excel to Access, to Oracle, to sqlServer, ...  Actually used SQLServer as the front end for Oracle once.

    Dan

    SELECT fld1, fld2, fld3 INTO myaccesstable FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
         'SELECT fld1, fld2, fld3 from myoracletable)


    Sorry, don't have the oracle connection info, but the connection info ? already exits ?
    • Edited by Dan_Kirk Thursday, March 1, 2012 1:33 PM
    Thursday, March 1, 2012 1:10 PM
  • Armin, it's possible with sqlServer, but if the task is in Access, might not work.

    What do you mean by "if the task is in Access"? If the task is in Oracle? I mean, he can make the Oracle engine do the job, not Access. (however via ODBC it would probably also work the other way, thought I'd prefer Oracle)

    Armin

    Thursday, March 1, 2012 1:17 PM
  • There is no function which says, 

    AccessDataBase = Oracle DataBase.

    But you can update the database like if it was done by an end user by:

    1. Create a new datatable
    2. Fill that with a Dataadapter from Access using a where clause and an OleDBDataAdapter
    3. Loop through the retrieved datatable from Oracle and update row by row the Access Datatable
    4. Update the DataBase using again the OleDBDataAdapter

    Quite simple and straight foreward in my perception.


    Success
    Cor

    Thursday, March 1, 2012 1:52 PM
  • Yes, Cor, that's probably the best way. 

    That was my first thought, to retrieve the Oracle table into a reader, loop through the records and insert the records into Access.  If one would want to use an SQLServer as the go between, the two commands,

    OPENDATASOURCE and OPENROWSET

    allow one to retrieve the records from Oracle and insert them into Access in one query.  Some casting and converting may be required.

    My neighbor purchased some three hundred cinder block, ten bags of mortar, several hundred feet of 1/4 inch rebar, and a few tons of mortar sand.  I told her she was now the proud owner of a new block wall, some assembly required.  It's that last part that's the doozy.  We worked for a month to build that wall.

    Thursday, March 1, 2012 2:09 PM

  • My neighbor purchased some three hundred cinder block, ten bags of mortar, several hundred feet of 1/4 inch rebar, and a few tons of mortar sand.  I told her she was now the proud owner of a new block wall, some assembly required.  It's that last part that's the doozy.  We worked for a month to build that wall.

    No that is not needed, for the Access part you can mainly use the DataSet wizard, the only thing you need probably is in a loop add the rows using a loop while you put the data from the datanames from Oracle into datanames of Access. The select and update are made for you using the designer so that are only two statements.

    You get those building machine parts for free.

    It becomes something more difficult if it is not a new wall but a wall which has to be reconstructed and you have to keep some old rocks while you polish those. Then you have to get the old rows and to update them with the data from Oracle. 

    :-)

    So probably not more than a one hour job including testing.

    :-)


    Success
    Cor



    Thursday, March 1, 2012 3:06 PM
  • It can be done directly using a single SQL statement. The syntax will differ depending upon whether the Access table already exists. The below example assumes that it does exist and the column names are the same. If the column names are different they will likely need to be specified:

           Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\Test Files\db1 XP.mdb;")
            AccessConn.Open()
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [LOG_TABLE] SELECT * FROM [SCHEMA_NAME.LOG_TABLE] IN '' [ODBC;Driver={Microsoft ODBC For Oracle};Server=servername;Uid=username;Pwd=password;] WHERE LOG_DATE_TIME > ?", AccessConn)
            Dim DateCriteria As Date = #9/30/2011#
            AccessCommand.Parameters.AddWithValue("DateVal", DateCriteria)
            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, March 1, 2012 3:17 PM