Update an Oracle table, get 3167 error on faked Autonumber ID field

[dhcomcast]

We're starting to use Oracle for the back-end instead of a separate
Access .mdb file for the data and everything as gone surprisingly well
so far. We are learning Oracle as we go; Yikes! But we have very
patient DBA's at the main office to answer newbie questions.

Using Access 2002 with linked ODBC tables to Oracle 9 (9i? not sure),
ODBC driver is SQORA32.DLL, verson 9.02.00.00.

My "Error 3167: Record is deleted" comes when I use .AddNew, .Update,
..Bookmark, then try to grab the "faked" Autonumber ID of the record
just entered, like I've done in Access many times before (code is
below).

For most things I have the users enter records into forms and
everything works just as it did with the tables in an Access back-end.
But for this table (long story) I have them click a button to get the
new record started, then I want to make that record current so they can
finish putting values in the rest of the fields.

This is how I've faked an Autonumber ID in Oracle:
1. set up the ID as primary key
2. set up a Sequence
3. set up a Trigger:
CREATE OR REPLACE TRIGGER MFD.BIFER_SIGLEGFEATURE_ID_PK
BEFORE INSERT
ON MFD.SIG_LEG_FEATURE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
SELECT siglegfeature_seq.NEXTVAL INTO :NEW.FEATURE_ID FROM dual;
END bifer_siglegfeature_id_pk;

This seems to make FEATURE_ID behave just like an Access Autonumber
field except in this instance.
' /\/\/\/\/\/\/\/ START CODE
Private Sub btnAddFeature_Click()
On Error GoTo Err_btnAddFeature_Click
Dim rst As Recordset
Dim rstClone As Recordset
Dim intID As Long

If IsNull(txtLegNbr) Then
MsgBox "Please select a Leg/Approach", , "NO LEG SELECTED"
Else

Set rst = CurrentDb.OpenRecordset("SIG_LEG_FEATURE")

With rst
.AddNew
.Fields![LEG_NUM] = txtLegNbr
.Fields![SYSTEM_ID_NO] = curSysID
.Update
.Bookmark = .LastModified
'** This next line throws the error 3167 **
intID = rst.Fields![FEATURE_ID]
End With

'** I'm kind of rusty on using bookmarks, too ...
Forms![frmMainSignals]![frmFeature].Form.Requery
Set rstClone = Me.RecordsetClone
rstClone.FindFirst "[FEATURE_ID] = " & intID
If rstClone.NoMatch Then
MsgBox "Not found"
Else
' Display the found record in the form.
Forms![frmMainSignals]![frmFeature].Form.Bookmark =
rstClone.Bookmark
End If

End If

Exit_btnAddFeature_Click:

On Error Resume Next
rst.Close
Set rst = Nothing
rstClone.Close
Set rstClone = Nothing

Exit Sub

Err_btnAddFeature_Click:
MsgBox "Error " & Err.Number & ": " & Err.DESCRIPTION,
vbInformation, "Add Feature ERROR"
Resume Exit_btnAddFeature_Click

End Sub
' /\/\/\/\/\/\/\/ END CODE

I've set a breakpoint at the top and checked the data in Oracle using
TOAD and the new record is really there and FEATURE_ID is filled in
with the next number in sequence as soon as the .Update line is run.
The .Bookmark line also runs fine. The Recordset just doesn't seem to
know that the value of FEATURE_ID is now filled in. So it calls it
deleted??

Should I be making a dbUseODBC workspace? I haven't had to deal with
workspaces before, when using Access back-ends.

Any advice on this would be greatly appreciated. THANKS!
Denise

[dhcomcast]

My References are:
1. VBA
2. Access 10 Object Library
3. DAO 3.6 Object Library
4. Office 10 Object Library (I don't usually include this but I'm doing
something elsewhere that needed it. Maybe Application.FileSearch?)

[Zlatko Matic]

Hello.

Why do you use before triger for autoincrement ID primary key ?
I don't know how it works with Oracle, but in PostgreSQL (should be similar
to Oracle) you can set field datatype to BIGSERIAL. The server then creates
the sequence itself, so there is no need for any trigger. I suppose that
there is something similar in Oracle...
I avoid triggers. I use them only for audit trail...

Bye.
<dh*******@yahoo.com> je napisao u poruci interesnoj
grupi:11**********************@g44g2000cwa.googleg roups.com...

We're starting to use Oracle for the back-end instead of a separate
Access .mdb file for the data and everything as gone surprisingly well
so far. We are learning Oracle as we go; Yikes! But we have very
patient DBA's at the main office to answer newbie questions.

Using Access 2002 with linked ODBC tables to Oracle 9 (9i? not sure),
ODBC driver is SQORA32.DLL, verson 9.02.00.00.

My "Error 3167: Record is deleted" comes when I use .AddNew, .Update,
.Bookmark, then try to grab the "faked" Autonumber ID of the record
just entered, like I've done in Access many times before (code is
below).

For most things I have the users enter records into forms and
everything works just as it did with the tables in an Access back-end.
But for this table (long story) I have them click a button to get the
new record started, then I want to make that record current so they can
finish putting values in the rest of the fields.

This is how I've faked an Autonumber ID in Oracle:
1. set up the ID as primary key
2. set up a Sequence
3. set up a Trigger:
CREATE OR REPLACE TRIGGER MFD.BIFER_SIGLEGFEATURE_ID_PK
BEFORE INSERT
ON MFD.SIG_LEG_FEATURE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
SELECT siglegfeature_seq.NEXTVAL INTO :NEW.FEATURE_ID FROM dual;
END bifer_siglegfeature_id_pk;

This seems to make FEATURE_ID behave just like an Access Autonumber
field except in this instance.
' /\/\/\/\/\/\/\/ START CODE
Private Sub btnAddFeature_Click()
On Error GoTo Err_btnAddFeature_Click
Dim rst As Recordset
Dim rstClone As Recordset
Dim intID As Long

If IsNull(txtLegNbr) Then
MsgBox "Please select a Leg/Approach", , "NO LEG SELECTED"
Else

Set rst = CurrentDb.OpenRecordset("SIG_LEG_FEATURE")

With rst
.AddNew
.Fields![LEG_NUM] = txtLegNbr
.Fields![SYSTEM_ID_NO] = curSysID
.Update
.Bookmark = .LastModified
'** This next line throws the error 3167 **
intID = rst.Fields![FEATURE_ID]
End With

'** I'm kind of rusty on using bookmarks, too ...
Forms![frmMainSignals]![frmFeature].Form.Requery
Set rstClone = Me.RecordsetClone
rstClone.FindFirst "[FEATURE_ID] = " & intID
If rstClone.NoMatch Then
MsgBox "Not found"
Else
' Display the found record in the form.
Forms![frmMainSignals]![frmFeature].Form.Bookmark =
rstClone.Bookmark
End If

End If

Exit_btnAddFeature_Click:

On Error Resume Next
rst.Close
Set rst = Nothing
rstClone.Close
Set rstClone = Nothing

Exit Sub

Err_btnAddFeature_Click:
MsgBox "Error " & Err.Number & ": " & Err.DESCRIPTION,
vbInformation, "Add Feature ERROR"
Resume Exit_btnAddFeature_Click

End Sub
' /\/\/\/\/\/\/\/ END CODE

I've set a breakpoint at the top and checked the data in Oracle using
TOAD and the new record is really there and FEATURE_ID is filled in
with the next number in sequence as soon as the .Update line is run.
The .Bookmark line also runs fine. The Recordset just doesn't seem to
know that the value of FEATURE_ID is now filled in. So it calls it
deleted??

Should I be making a dbUseODBC workspace? I haven't had to deal with
workspaces before, when using Access back-ends.

Any advice on this would be greatly appreciated. THANKS!
Denise

[Tim Marshall]

dh*******@yahoo.com wrote:

We're starting to use Oracle for the back-end instead of a separate
I don't use Oracle as linked tables in Access, I tend to use pass
through queries and will also be using ADO eventually. So I don't
really have anything Oracle related to suggest.
CREATE OR REPLACE TRIGGER MFD.BIFER_SIGLEGFEATURE_ID_PK
BEFORE INSERT
ON MFD.SIG_LEG_FEATURE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
SELECT siglegfeature_seq.NEXTVAL INTO :NEW.FEATURE_ID FROM dual;
END bifer_siglegfeature_id_pk;
I don't use the DECLARE line nor do I qualify the end statement with the
name of the trigger as you've done here, but it's working so I guess
it's OK.
With rst
.AddNew
.Fields![LEG_NUM] = txtLegNbr
.Fields![SYSTEM_ID_NO] = curSysID
.Update
.Bookmark = .LastModified
'** This next line throws the error 3167 **
intID = rst.Fields![FEATURE_ID]
End With
Have you tried removing the rst? It seems to me the line causing the
error should be:

intID = .Fields![FEATURE_ID]
Should I be making a dbUseODBC workspace? I haven't had to deal with
workspaces before, when using Access back-ends.

I've never bothered.

Not much help, I know, but I take interest in Oracle related questions
and I saw this one had not been answered.

On Zlatko's post, you do need a trigger to incorporate a sequence with a
table.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[DAH200]

Thanks Zlatko and Tim for responses.

I've worked around trying to find out what the Feature_ID is on the
newly inserted record. Instead I show them a small form that makes them
define the last piece I need to make a unique new record. Then I insert
the record and show them the main form which they can tweak at this
point. I don't think removing the superfluous rst made a difference.

The working code is below.
Thanks again!
Denise

Private Sub btnAddFeature_Click()
On Error GoTo Err_btnAddFeature_Click
Dim rst As Recordset
Dim rstClone As Recordset
Dim intID As Long
Dim varNewRec As Variant

intFeatureID = 0 ' set as Public in MainMod, stays 0 if user
cancels frmNewFeature

If IsNull(txtLegNbr) Then
MsgBox "Please select a Leg/Approach", , "NO LEG SELECTED"
Else
DoCmd.OpenForm "frmNewFeature", , , , , acDialog

If intFeatureID <> 0 Then

Set rst = CurrentDb.OpenRecordset("SIG_LEG_FEATURE",
dbOpenDynaset)

With rst
.AddNew
.Fields![SYSTEM_ID_NO] = curSysID
.Fields![LEG_NUM] = txtLegNbr
.Fields![FEATURE_TYPE_ID] = intFeatureID
.Fields![INSTALLED_DT] = Date
.Update
End With

Me.Requery
Me.Refresh
Set rstClone = Me!frmFeature.Form.RecordsetClone
rstClone.FindFirst "[SYSTEM_ID_NO] = " & curSysID & " and
[LEG_NUM] = " & txtLegNbr & " and [FEATURE_TYPE_ID] = " & intFeatureID
If rstClone.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me!frmFeature.Form.Bookmark = rstClone.Bookmark
Me!frmFeature.Form.Controls!INSTALLED_DT.SetFocus
End If

End If ' end of if intFeatureID <> 0

End If ' end of if isNull txtLegNbr
Exit_btnAddFeature_Click:

On Error Resume Next
rst.Close
Set rst = Nothing
rstClone.Close
Set rstClone = Nothing

Exit Sub

Err_btnAddFeature_Click:
If Err.Number = 3146 Then
MsgBox "That feature already entered for this approach. No
action taken", vbInformation, "Feature Exists"
Else
MsgBox "Error " & Err.Number & ": " & Err.DESCRIPTION,
vbInformation, "Add Feature ERROR"
End If
Resume Exit_btnAddFeature_Click

End Sub