Updating an Oracle table using Ms access export


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Updating an Oracle table using Ms access export

  1. #1
    kala Guest

    Updating an Oracle table using Ms access export


    Hi all,
    Is it possible to update an existing Oracle table with ms access data? I
    know that export to external is one feature that I can use. But that will
    not update the table, but will only create a new table, as of my knowledge.
    So can anybody suggest me a solution? User can get data from MS access database
    and he wants to update Oracle database with the available info from access.
    Any help will be appreaciated.
    Thanks,
    Kala

  2. #2
    shobhan Guest

    Re: Updating an Oracle table using Ms access export


    "kala" <kalakurup@yahoo.com> wrote:
    >
    >Hi all,
    > Is it possible to update an existing Oracle table with ms access data?

    I
    >know that export to external is one feature that I can use. But that will
    >not update the table, but will only create a new table, as of my knowledge.
    >So can anybody suggest me a solution? User can get data from MS access database
    >and he wants to update Oracle database with the available info from access.
    >Any help will be appreaciated.
    >Thanks,
    >Kala

    hi kala ,
    if u are using vb as frontend ,,, then my solution might help u

    using the recordsets u can update the existing oracle table .
    logical steps
    open a recordset1 to retreive the values from the msacess database
    update the oracle table using query or another recordset using the
    recordset1
    bye

  3. #3
    kala Guest

    Re: Updating an Oracle table using Ms access export


    Thanks Shobhan,
    But I was looking for a feature in msaccess directly to do it. currently
    there is no GUI using to fill up the msaccess table.
    Kala

    "shobhan" <shobhangupta@hotmail.com> wrote:
    >
    >"kala" <kalakurup@yahoo.com> wrote:
    >>
    >>Hi all,
    >> Is it possible to update an existing Oracle table with ms access data?

    >I
    >>know that export to external is one feature that I can use. But that will
    >>not update the table, but will only create a new table, as of my knowledge.
    >>So can anybody suggest me a solution? User can get data from MS access

    database
    >>and he wants to update Oracle database with the available info from access.
    >>Any help will be appreaciated.
    >>Thanks,
    >>Kala

    > hi kala ,
    > if u are using vb as frontend ,,, then my solution might help u
    >
    > using the recordsets u can update the existing oracle table .
    > logical steps
    > open a recordset1 to retreive the values from the msacess database
    > update the oracle table using query or another recordset using the
    > recordset1
    > bye



  4. #4
    Chris Hylton Guest

    Re: Updating an Oracle table using Ms access export


    You can do this directly in Access by using linked tables to Oracle. If you
    link to the Oracle table (from inside Access), you can run insert/update
    SQL statements directly to the Oracle table. Of course you'll need the Oracle
    client stuff installed on the PC in question.

    If you need to do something more complex to process the records into Oracle
    (other than a SQL command), you can still (even without the pure VB front-end)
    write code in an Access module to handle the inserts/updates in a loop with
    a recordset (as already mentioned by the other response above).

    Chris

    "kala" <kalakurup@yahoo.com> wrote:
    >
    >Thanks Shobhan,
    >But I was looking for a feature in msaccess directly to do it. currently
    >there is no GUI using to fill up the msaccess table.
    >Kala
    >
    >"shobhan" <shobhangupta@hotmail.com> wrote:
    >>
    >>"kala" <kalakurup@yahoo.com> wrote:
    >>>
    >>>Hi all,
    >>> Is it possible to update an existing Oracle table with ms access data?

    >>I
    >>>know that export to external is one feature that I can use. But that will
    >>>not update the table, but will only create a new table, as of my knowledge.
    >>>So can anybody suggest me a solution? User can get data from MS access

    >database
    >>>and he wants to update Oracle database with the available info from access.
    >>>Any help will be appreaciated.
    >>>Thanks,
    >>>Kala

    >> hi kala ,
    >> if u are using vb as frontend ,,, then my solution might help u
    >>
    >> using the recordsets u can update the existing oracle table .
    >> logical steps
    >> open a recordset1 to retreive the values from the msacess database
    >> update the oracle table using query or another recordset using the
    >> recordset1
    >> bye

    >



  5. #5
    kala Guest

    Re: Updating an Oracle table using Ms access export


    Chris,
    I am running in to a problem, while doing this linking. I am using Microsoft
    ODBC Driver for Oracle to create DSN. I could link to the table, but not
    been able to update table there. But using the same login, if I insert a
    row through SQl plus from my workstation, it works fine. But why cann't I
    do it from access with same login? Can you please help me?
    thanks,
    Kala

    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >You can do this directly in Access by using linked tables to Oracle. If

    you
    >link to the Oracle table (from inside Access), you can run insert/update
    >SQL statements directly to the Oracle table. Of course you'll need the

    Oracle
    >client stuff installed on the PC in question.
    >
    >If you need to do something more complex to process the records into Oracle
    >(other than a SQL command), you can still (even without the pure VB front-end)
    >write code in an Access module to handle the inserts/updates in a loop with
    >a recordset (as already mentioned by the other response above).
    >
    >Chris
    >
    >"kala" <kalakurup@yahoo.com> wrote:
    >>
    >>Thanks Shobhan,
    >>But I was looking for a feature in msaccess directly to do it. currently
    >>there is no GUI using to fill up the msaccess table.
    >>Kala
    >>
    >>"shobhan" <shobhangupta@hotmail.com> wrote:
    >>>
    >>>"kala" <kalakurup@yahoo.com> wrote:
    >>>>
    >>>>Hi all,
    >>>> Is it possible to update an existing Oracle table with ms access data?
    >>>I
    >>>>know that export to external is one feature that I can use. But that

    will
    >>>>not update the table, but will only create a new table, as of my knowledge.
    >>>>So can anybody suggest me a solution? User can get data from MS access

    >>database
    >>>>and he wants to update Oracle database with the available info from access.
    >>>>Any help will be appreaciated.
    >>>>Thanks,
    >>>>Kala
    >>> hi kala ,
    >>> if u are using vb as frontend ,,, then my solution might help u
    >>>
    >>> using the recordsets u can update the existing oracle table .
    >>> logical steps
    >>> open a recordset1 to retreive the values from the msacess database
    >>> update the oracle table using query or another recordset using the


    >>> recordset1
    >>> bye

    >>

    >



  6. #6
    Chris Hylton Guest

    Re: Updating an Oracle table using Ms access export


    Off the top of my head, I don't know...if you have all of the Oracle client
    stuff installed, try the Oracle ODBC driver instead of the Microsoft one...see
    if that changes the results.

    Are you trying to directly open the linked table and edit a value or are
    you actually trying to run a SQL insert or update from a local Access table
    to a linked Oracle table ?

    You may end up needing to do 'passthru' query from Access and run direct
    insert statements against the ODBC connection. This won't even use the linked
    table scenario I mentioned below and will pass direct SQL commands through
    ODBC to Oracle.

    I don't have Access installed here at my client site...if I get the time
    over the weekend I'll do it from home over VPN and see if I get the same
    results.

    Sorry...wish I had a better answer for you right now...

    Chris

    "kala" <kalakurup@yahoo.com> wrote:
    >
    >Chris,
    > I am running in to a problem, while doing this linking. I am using Microsoft
    >ODBC Driver for Oracle to create DSN. I could link to the table, but not
    >been able to update table there. But using the same login, if I insert a
    >row through SQl plus from my workstation, it works fine. But why cann't

    I
    >do it from access with same login? Can you please help me?
    >thanks,
    >Kala
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>You can do this directly in Access by using linked tables to Oracle. If

    >you
    >>link to the Oracle table (from inside Access), you can run insert/update
    >>SQL statements directly to the Oracle table. Of course you'll need the

    >Oracle
    >>client stuff installed on the PC in question.
    >>
    >>If you need to do something more complex to process the records into Oracle
    >>(other than a SQL command), you can still (even without the pure VB front-end)
    >>write code in an Access module to handle the inserts/updates in a loop

    with
    >>a recordset (as already mentioned by the other response above).
    >>
    >>Chris
    >>
    >>"kala" <kalakurup@yahoo.com> wrote:
    >>>
    >>>Thanks Shobhan,
    >>>But I was looking for a feature in msaccess directly to do it. currently
    >>>there is no GUI using to fill up the msaccess table.
    >>>Kala
    >>>
    >>>"shobhan" <shobhangupta@hotmail.com> wrote:
    >>>>
    >>>>"kala" <kalakurup@yahoo.com> wrote:
    >>>>>
    >>>>>Hi all,
    >>>>> Is it possible to update an existing Oracle table with ms access data?
    >>>>I
    >>>>>know that export to external is one feature that I can use. But that

    >will
    >>>>>not update the table, but will only create a new table, as of my knowledge.
    >>>>>So can anybody suggest me a solution? User can get data from MS access
    >>>database
    >>>>>and he wants to update Oracle database with the available info from

    access.
    >>>>>Any help will be appreaciated.
    >>>>>Thanks,
    >>>>>Kala
    >>>> hi kala ,
    >>>> if u are using vb as frontend ,,, then my solution might help u
    >>>>
    >>>> using the recordsets u can update the existing oracle table .
    >>>> logical steps
    >>>> open a recordset1 to retreive the values from the msacess database
    >>>> update the oracle table using query or another recordset using the


    >
    >>>> recordset1
    >>>> bye
    >>>

    >>

    >



  7. #7
    Chris Hylton Guest

    Re: Updating an Oracle table using Ms access export


    Just located a machine here at work that has Access (97 I think it was) installed.
    I linked to an Oracle table using the Microsoft ODBC driver for Oracle and
    I could simply open the table in Access and edit it just fine. In fact,
    even the trigger 'fired' as expected on the table and caused another field
    to be updated with my username when I changed a record.

    The first place I'd look would be the version of your driver...might just
    be old...but the Microsoft driver here on the machine I tested this on was
    from march 2001 (version 2.573.something).

    Then next place would be security/grants in Oracle...but if you can hit it
    from SQL Plus w/ the same userid, then I doubt this is the problem.

    Very strange...

    Chris

    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >Off the top of my head, I don't know...if you have all of the Oracle client
    >stuff installed, try the Oracle ODBC driver instead of the Microsoft one...see
    >if that changes the results.
    >
    >Are you trying to directly open the linked table and edit a value or are
    >you actually trying to run a SQL insert or update from a local Access table
    >to a linked Oracle table ?
    >
    >You may end up needing to do 'passthru' query from Access and run direct
    >insert statements against the ODBC connection. This won't even use the

    linked
    >table scenario I mentioned below and will pass direct SQL commands through
    >ODBC to Oracle.
    >
    >I don't have Access installed here at my client site...if I get the time
    >over the weekend I'll do it from home over VPN and see if I get the same
    >results.
    >
    >Sorry...wish I had a better answer for you right now...
    >
    >Chris
    >
    >"kala" <kalakurup@yahoo.com> wrote:
    >>
    >>Chris,
    >> I am running in to a problem, while doing this linking. I am using Microsoft
    >>ODBC Driver for Oracle to create DSN. I could link to the table, but not
    >>been able to update table there. But using the same login, if I insert

    a
    >>row through SQl plus from my workstation, it works fine. But why cann't

    >I
    >>do it from access with same login? Can you please help me?
    >>thanks,
    >>Kala
    >>
    >>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>
    >>>You can do this directly in Access by using linked tables to Oracle.

    If
    >>you
    >>>link to the Oracle table (from inside Access), you can run insert/update
    >>>SQL statements directly to the Oracle table. Of course you'll need the

    >>Oracle
    >>>client stuff installed on the PC in question.
    >>>
    >>>If you need to do something more complex to process the records into Oracle
    >>>(other than a SQL command), you can still (even without the pure VB front-end)
    >>>write code in an Access module to handle the inserts/updates in a loop

    >with
    >>>a recordset (as already mentioned by the other response above).
    >>>
    >>>Chris
    >>>
    >>>"kala" <kalakurup@yahoo.com> wrote:
    >>>>
    >>>>Thanks Shobhan,
    >>>>But I was looking for a feature in msaccess directly to do it. currently
    >>>>there is no GUI using to fill up the msaccess table.
    >>>>Kala
    >>>>
    >>>>"shobhan" <shobhangupta@hotmail.com> wrote:
    >>>>>
    >>>>>"kala" <kalakurup@yahoo.com> wrote:
    >>>>>>
    >>>>>>Hi all,
    >>>>>> Is it possible to update an existing Oracle table with ms access data?
    >>>>>I
    >>>>>>know that export to external is one feature that I can use. But that

    >>will
    >>>>>>not update the table, but will only create a new table, as of my knowledge.
    >>>>>>So can anybody suggest me a solution? User can get data from MS access
    >>>>database
    >>>>>>and he wants to update Oracle database with the available info from

    >access.
    >>>>>>Any help will be appreaciated.
    >>>>>>Thanks,
    >>>>>>Kala
    >>>>> hi kala ,
    >>>>> if u are using vb as frontend ,,, then my solution might help u
    >>>>>
    >>>>> using the recordsets u can update the existing oracle table .
    >>>>> logical steps
    >>>>> open a recordset1 to retreive the values from the msacess database
    >>>>> update the oracle table using query or another recordset using the


    >
    >>
    >>>>> recordset1
    >>>>> bye
    >>>>
    >>>

    >>

    >



  8. #8
    JOhn Maddox Guest

    Re: Updating an Oracle table using Ms access export


    "kala" <kalakurup@yahoo.com> wrote:
    >
    >Chris,
    > I am running in to a problem, while doing this linking. I am using Microsoft
    >ODBC Driver for Oracle to create DSN. I could link to the table, but not
    >been able to update table there. But using the same login, if I insert a
    >row through SQl plus from my workstation, it works fine. But why cann't

    I
    >do it from access with same login? Can you please help me?
    >thanks,
    >Kala


    This has happenned to me as well. In my case it turned out to be that when
    I linked my tables using Access I did not tell Access which record should
    have been the key. It turns out that not assigning a key field meant I could
    not update the tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center