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
