Access and Oracle sequence issue

[peter.morin]

Issue: I am inserting an Oracle record containing insert trigger via
Access 2002 using the code below. The issue is that the sequence from
the acSaveRecord is not reflected after the insert so the insert into
the second table section: ' Copy LOB entries now. fails on unique
index constraint (understandable because it has the orig rate_id).

The strange thing is that it works fine for new records that have
beend added and then cloned using the source below but not for
acess-to-oracle converted records.
Is there a way so save the seq value to a variable? I need to make
sure it is the one I just assigned via the Oracle trigger.

Table relationship: CMPLY_FUND_RATE _DETAIL (1) ---
CMPLY_FUND_LOB( Many)

Source:
Private Sub cmdNewRate_Clic k()
On Error GoTo Err_cmdNewRate_ Click

Dim txtAccount As String
Dim txtFundName As String
Dim lRateID As Long
Dim txtSQLStatement As String

If (Me![EXP_DATE] <= Me![EFF_DATE]) Then
MsgBox "Please confirm Effective and Expiration Dates. The
Expiration Date is prior or equal to the the Effective Date.",
vbInformation + vbOKOnly
Me![EXP_DATE].SetFocus
GoTo Exit_cmdNewRate _Click
End If
lRateID = Me![RATE_ID]
txtAccount = Me![ACCOUNT]
txtFundName = Me![FUND_NAME]
DoCmd.GoToRecor d , , acNewRec

' Set Key Fields and copy over existing LOB from current rate
Me![FUND_NAME] = txtFundName
Me![ACCOUNT] = txtAccount

' Need to Save the keys info prior to copying LOB's
DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
--->>> *** this should generate a new rate_id (insert trigger
on CMPLY_FUND_RATE _DETAIL which
the form is tied to)

' Copy LOB entries now.
txtSQLStatement = "INSERT INTO CMPLY_FUND_LOB ( RATE_ID,
[LINE_OF_BUSINES S] )"
txtSQLStatement = txtSQLStatement & " SELECT RATE_ID ,
LINE_OF_BUSINES S"
txtSQLStatement = txtSQLStatement & " FROM CMPLY_FUND_LOB WHERE
RATE_ID = " & lRateID

DoCmd.RunSQL txtSQLStatement , True

Me![lstActiveLOB].Requery
Me![RATE].SetFocus

Exit_cmdNewRate _Click:
Exit Sub

Err_cmdNewRate_ Click:
MsgBox Err.Description
Resume Exit_cmdNewRate _Click

End Sub

[Tim Marshall]

pe*********@cna .com wrote:

Is there a way so save the seq value to a variable? I need to make
sure it is the one I just assigned via the Oracle trigger.

Source:
Private Sub cmdNewRate_Clic k()
On Error GoTo Err_cmdNewRate_ Click

Dim txtAccount As String
Dim txtFundName As String
Dim lRateID As Long
Dim txtSQLStatement As String

If (Me![EXP_DATE] <= Me![EFF_DATE]) Then
MsgBox "Please confirm Effective and Expiration Dates. The
Expiration Date is prior or equal to the the Effective Date.",
vbInformation + vbOKOnly
Me![EXP_DATE].SetFocus
GoTo Exit_cmdNewRate _Click
End If

lRateID = Me![RATE_ID]
txtAccount = Me![ACCOUNT]
txtFundName = Me![FUND_NAME]
So, it seems that you already have a current record in the Oracle table.
Perhaps selected on a form or dta sheet or even a list box? And you
are trying to copy this record?
DoCmd.GoToRecor d , , acNewRec
Here comes the new record....
' Set Key Fields and copy over existing LOB from current rate
Me![FUND_NAME] = txtFundName
Me![ACCOUNT] = txtAccount ' Need to Save the keys info prior to copying LOB's
DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Now, you're taking the previous values and putting them into a new
record. This is where the Oracle trigger kicks in and creates a new ID
for RATE_ID.

All fine, so far.
--->>> *** this should generate a new rate_id (insert trigger
on CMPLY_FUND_RATE _DETAIL which
the form is tied to)
Correct, as per my above comments.
' Copy LOB entries now.
txtSQLStatement = "INSERT INTO CMPLY_FUND_LOB ( RATE_ID,
[LINE_OF_BUSINES S] )"
txtSQLStatement = txtSQLStatement & " SELECT RATE_ID ,
LINE_OF_BUSINES S"
txtSQLStatement = txtSQLStatement & " FROM CMPLY_FUND_LOB WHERE
RATE_ID = " & lRateID

DoCmd.RunSQL txtSQLStatement , True

This looks like an SQL statement which does exactly the same as the
steps you've done above. You're inserting into CMPLY_FUND_LOB with a
select statement from CMPLY_FUND_LOB. So you're trying to create a new
record, twice?

It seems to me the reason this insert is failing is because you are
trying to insert a value for RATE_ID. If there is a trigger for this
value, I would think Oracle might cough at this, especially if the
trigger involves "before insert". With "after insert", it might be OK,
just change the value of the field, but I'm not sure.

Secondly, is RATE_ID a PK? Does it have a primary key constraint? This
is the same as a primary key in Access/Jet. If this is so, your
statement is failing because you're trying to enter the same primary key
value, let alone forcing a value where a trigger performs the function.

I personally prefer to do this sort of thing with Oracle Pass-through
queries, myself, using DAO methods (though I'm soon going to venture
into DSNless land with ADO). If you can tell me exactly what you're
trying to do, I can write some code that will do this - it's pretty
straightforward .

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[peter.morin]

....Copy LOB entries
this code inserts record into cmply_Lob with the rate_id(unique
key rate_id + LOB) using the rate_id from cmply_fund_deta il(from before
insert trigger). The lRATEID is the rate_id from the orig record that
I am trying to clone in the LOB table.

ex. if the orig rate_id = 10 and the new = 10O then any record in LOB
with rate_id 11 will now be inserted into cmply_LOB table with rate_id
100.

[Tim Marshall]

pe*********@cna .com wrote:

...Copy LOB entries
this code inserts record into cmply_Lob with the rate_id(unique
key rate_id + LOB) using the rate_id from cmply_fund_deta il(from before
insert trigger). The lRATEID is the rate_id from the orig record that
I am trying to clone in the LOB table.

ex. if the orig rate_id = 10 and the new = 10O then any record in LOB
with rate_id 11 will now be inserted into cmply_LOB table with rate_id
100.

But peter, it seems to me that this SQL:

txtSQLStatement = "INSERT INTO CMPLY_FUND_LOB ( RATE_ID,
[LINE_OF_BUSINES S] )"
txtSQLStatement = txtSQLStatement & " SELECT RATE_ID ,
LINE_OF_BUSINES S"
txtSQLStatement = txtSQLStatement & " FROM CMPLY_FUND_LOB WHERE
RATE_ID = " & lRateID

Comes out to an SQL statemet which says:

INSERT INTO CMPLY_FUND_LOB ( RATE_ID, [LINE_OF_BUSINES S] ) SELECT
RATE_ID, LINE_OF_BUSINES S FROM CMPLY_FUND_LOB WHERE RATE_ID = <long>

And <long> is the same rate_ID that was the rate_id from the originally
selected record. I must be missing something. In your last post, you
say you're inserting into CMPLY_LOB, but the above shows you're
inserting into CMPLY_FUND_LOB from CMPLY_FUND_LOB.

The lRateID has not changed from the originally selected record. Is the
original selection in another table? I wouldn't be so confused if your
SQL had the select clause coming from CMPLY_LOB....

Sorry 8(

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[peter.morin]

I was actually trying to figure out why the existing access code using
assaverecrod does not always return the new seq number. I was hoping
it was related to oracle.

I guess I may just have to code my own insert using a pass thru..

I was able to to the pass thru but how do I get the new seq...it
appears that
txtSQLDetail = "INSERT INTO CMPLY_FUND_DETA IL ( RATE_ID) VALUES
(CMPLY_FUND_RAT E_DETAIL_SEQ.NE XTVAL)"
txtSQLDetail = txtSQLDetail & " returning RATE_ID into :" & nRateId

inserts the record bu I don't get the rate_id sequence back

[peter.morin]

I meant to type cmply_fund_LOB.

From the source the lrateid is the curret rateid on the screen from the

original record. This rateid is the one that is used to create cloned
records in the cmply_fund_LOB table with a new rateid(assigned from an
insert trigger on the cmply_fund_LOB table).

[peter.morin]

Strange....I recompiled the before insert trigger on the
CMPLY_FUND_RATE _DETAIL table and all appears to be working. Any
suggestion as to why this will appear to fix the issue.

[peter.morin]

I lied. that didn't fix all records. I quess I will need to write my
own insert into the cmply_fund_rate _detail table. Is there a way to
bring back the sequence number I just used?