I am unable to update a record which is there in oracle
by using the odbc
if i directly use a update statement it works eg
res.sql = "update emp set...."

but if i use a record set and addrow and update
it is not working
res.sql = "select * from emp"
res.setvalue("empno", "1234")
this code is not working

if my database is on local machine it works with MS Access
if is on a remote machine it is giving an error
please help me out
My application depends on two databases MS ACCESS and Oracle

if MS Access mdb is on the local machine then my updaterow works fine
id MS ACCESS mdb is on the remote machine my updaterow does not function

in the case of oracle updaterow does not work at all
but if i use
qry.sql = "update emp set....." then it works

but if i get a record into the recordset and try ton update using update row it does not update

I am using 4.6 version

please hel me out
Who is Participating?
AntonysinghConnect With a Mentor Commented:

To insert or update a Access table, the access table should be in your loacal machine from which you are running the script and dsn shold points to that table OR

Your access table should be in Lotusnotes server and the dsn in the lotusnotes server should points to that table.  

 you can not access any Access table other than these two method.

Regard updaten row method, I have worked in that with Access table , it is working fine. The sample code is given below.

     Dim conn As New ODBCConnection
     Dim qry As New ODBCQuery
     Dim result As New ODBCResultSet

     'Connect to database through DSN entry , if success    
      proceed else exit
     If Not conn.ConnectTo ("TestDSN") Then
          e = conn.GetError
          emsg = conn.GetExtendedErrorMessage(e)
          Msgbox emsg
          Exit Sub
     End If

     sql = "select * from Emp"
     Set qry.Connection = conn
     qry.SQL = sql
     Set result.query = qry
     flag = result.Execute()
     f = result.setvalue("Name", "ant222")
     f = result.setvalue("No", "112121")

I don't have Oracle to test this.

Hi Rajanb,

This sees to be problem with your dsn. Please check, to update a remote database , you should have dsn in lotus notes server rather in the client.

If all are correct and still you getting error , Please give me the error message.


Which version of Notes are you using?

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

I forgot, could you explain more precise what do you mean by local access and remote access?
rajanbAuthor Commented:
Edited text of question.
I don't want to be a pain in the ... but could you tell me as well the exact wording of the SQL statement using "update".

Also, you are creating a new row in your second example, but "update" doesn't "insert" a new row (as far as I know). So are you sure that you're doing the right (same) thing in both cases?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.