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