Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Munich, Germany
    Posts
    35

    Unanswered: Access VBA problem with Oracle connection

    Hi everyone,

    I am using Access as an interface to an SQL Server database and an Oracle database.

    What I do is I insert a new record in an ADODB recordset. Here is how I do it in SQL server:

    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("select * from EAM_T where EAMID=someNum, dbOpenDynaset, dbSeeChanges)
    Dim rs2 As ADODB.Recordset
    Set rs2 = New ADODB.Recordset

    rs2.Open "Select * from EAM_T", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    rs2.AddNew
    rs2![Name] = rs![Name]
    rs2.Update
    newID = rs2!EAMlID
    rs2.Close

    This always works fine in SQL server. Upon browsing a lot of documentation I found that you have to use Requery in Oracle for this to work. So, here is how I do it in Oracle:

    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("select * from EAM_T where EAMID=someNum, dbOpenDynaset, dbSeeChanges)
    Dim rs2 As ADODB.Recordset
    Set rs2 = New ADODB.Recordset

    rs2.Open "Select * from EAM_T", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    rs2.AddNew
    rs2![Name] = rs![Name]
    rs2.Update
    bookmark = rs2.AbsolutePosition
    rs2.Requery
    rs2.AbsolutePosition = bookmark
    newID = rs2!EAMID

    This works most of the time. But sometimes it returns another ID, not the one that just existed. So, the newID is inserted in some position that is not the same after the requery. How can I make sure that the new record is always inserted at the end and this requery works fine. Basically, all I am trying to so is get the field value of a newly inserted row in Oracle.

    Thanks,

    Pankaj

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Actually, you dont have to use .requery to this approach. All you have to use is a TABLE recordset (not a query recordset, like the one you are insert-- that is, if you are only going to insert values). The reason is returning another ID is because you called the .update method before assigning the newid to your variable.

  3. #3
    Join Date
    Jan 2004
    Location
    Munich, Germany
    Posts
    35
    Hi,

    Could you post a small sample, if it is not too much to ask?

    Thanks,
    Pankaj

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >This works most of the time. But sometimes it returns another ID,
    If this is really 100% correct, I'd conclude it is NOT a database problem, per se.
    Oracle will not arbitrarily to work sometimes & not others.
    It may be a data dependent error.
    IMO, the source of the problem is not inherent to the Oracle DB.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Munich, Germany
    Posts
    35
    I am not exactly sure what is happening. I think between the Update abd Requery, the position of the newly inserted row changes.

    No clue man. I have been bitten bad by this bug. Deleted a wrong key.

    Pankaj

Posting Permissions

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