Updating an Oracle table using Ms access export

[kala]

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

[shobhan]

"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

[kala]

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

[Chris Hylton]

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
>

[kala]

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
>>
>

[Chris Hylton]

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
>>>
>>
>

[Chris Hylton]

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
>>>>
>>>
>>
>

[JOhn Maddox]

"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.